Previous Topic Next Topic

Macro Help

Closed
Shaan - Apr 16, 2010 at 03:07 AM
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 - Apr 21, 2010 at 07:18 AM
Hi

I have a query in macro.

Now i have excel with two columns.
eg: a b
b a
a c
d a
b k

now i wanted to check duplicates in both column.
results has to display in column 3 & 4.

condition is as below:
1. in column 3 if a character exist in column 1 not in column 2
2. in column 4 if a character exist in column 2 not in column 1
3. in column 3, if a character exist more than one times in column 3 than column 4(i.e duplicates characters)

I know its tricky, please help me

7 replies

Answer 1 / 7
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 770
Apr 16, 2010 at 05:45 AM
based on your sample, what is the expected answer
0
Answer 2 / 7
Shann
Apr 18, 2010 at 09:21 PM
answer should be : 3rd coulumn 4th column
b c
d k
0
Answer 3 / 7
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 770
Apr 19, 2010 at 03:03 AM
Could you explain how this 4 rows became one. I am not able to see the pattern
You gave this example

eg: a b
b a
a c
d a
b k

Now could you tell what value is for each row could appear in column 3 and 4 as you asked.
0
Answer 4 / 7
Shann
Apr 19, 2010 at 03:12 AM
k. I will tell why answer is column3 column 4
b c
d k

the character b(2times) exist in column 1, but exist one time in column 2
the character d not exist in column 2
the character c not exist in column 1
the character k not exist in column 1
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 7
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 770
Apr 19, 2010 at 03:58 AM
What you mean here

3. in column 3, if a character exist more than one times in column 3 than column 4(i.e duplicates characters)

Also when data is copied, are you looking to copy unique values only, so that all values in column 3 are unique and also same for column 4
0
Answer 6 / 7
Shaan
Apr 20, 2010 at 10:45 PM
k. i will tell you in simple way

i have two columns. the number or words that exist in column A may exist in column B.(Duplicate).

But what i have to capture is: If a word exist in another column fine, no issue.

Suppose if a word exist more than one time in a column, that word i have to copy to seperate column.

Note: Only a word or number that exist both Column A and B and again the word exist in Column A.

Simple example is:

Column A Column B
1 1
2 2
1

Answer is : 1 , becuase it appears 2 time in column A and appears one time in Column B
but 2 is exist both column A and B one time. So i don't want that count.
0
Answer 7 / 7
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 770
Apr 21, 2010 at 07:18 AM
Well I am still confused about exactly what you want. Any ways look at this formula

=IF(COUNTIF(B:B,A2)=0, A2 & " is only in column A", IF(COUNTIF(A:A,A2) - COUNTIF(B:B,A2)= 1, A2 & " appears 1 time more in column A then in column B", ""))

if you copy it in cell C2 (provided the data in column A and B and starts from A2 and B2 respectively


COUNTIF(B:B,A1)=0 is counting column B, if the value exists in cell A2. If the count is 0, it means that the value in cell A2 only appears in column A2

COUNTIF(A:A,A2) - COUNTIF(B:B,A2)= 1 is showing the when difference in the count is 1
0