Macro Help Closed

Question

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

rizvisa1 · Answer

based on your sample, what is the expected answer

Shann · Answer

answer should be : 3rd coulumn      4th column
                                          b                             c
                                          d                              k

rizvisa1 · Answer

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.

Shann · Answer

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

rizvisa1 · Answer

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

Shaan · Answer

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.

rizvisa1 · Answer

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

Macro Help

7 responses

Similar discussions

Subscribe To Our Newsletter!