I have 2 columns A & B. Need to compare A value with entire B and print count new Column C
I have 2 columns A & B. Need to compare A value with entire B and print count new Column C.
That is for each value in A column should be compared with entire B Column values and print the count of A column value occurrence in B column as shown below..
Actually all values are alphanumeric values
Example,
A B C (Expected output)
89 10 0
10 63 1
55 63 0
89 63 0
63 8 3
9 2 0
10 6 1
Can anyone help me ?
Last edited by raav05; 10-17-2012 at 04:42 PM.
Assuming Data is in A2:C8
In C2
=COUNTIF($B$2:$B$8,A2) copied down.
Is that what you are looking for?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Sorry. Its not giving the expected results..
result of your formula..
A B C ( output)
89 10 0
10 63 0
55 63 0
89 63 3
63 8 0
9 2 0
10 6 0
But my expected out put is
A B C (Expected output)
89 10 0
10 63 1
55 63 0
89 63 0
63 8 3
9 2 0
10 6 1
See example attached.
This formula will work for alphanumeric also
I got it.. Actually a wide space found in the column A. So that formula is not working for alpha numeric..
You are great.. Thanks a lot.. Its solved
Why do you expect a count of 1 in Col C when Col A = 10? There are no 10's in Col B?
EDIT: My mistake
Seems you have my formula set up wrong.
Do your values begin in A2 or A1? My example assumed A2. You'd need to modify
Last edited by ChemistB; 10-17-2012 at 04:26 PM.
10 is there in column B..However your formula works for numeric number..Can you please provide it for alphanumeric as well
A B C
89 10 0
10 63 1
55 63 0
89 63 0
63 8 3
9 2 0
10 6 1
A B C (Expected output
*U121491303 U121491303 1
*C220336631 C220336631 1
*J221298574 J221298574 1
*L122028913 L122028913 1
*L122029330 L122029330 2
*L103370992 L103370992 1
*L122027998 L122029330 0
It works great for numeric Values. Could please help for aplha numeric or text values.??
Are the values in A and B the same or do the values in Col A have an * in front of them like your example?
If there are spaces in front of the values in Col A
try
=COUNTIF($B$2:$B$8: TRIM(A2))
OR
Select Col A and CNTRL H (to bring up FIND & REPLACE)
Enter a space for "Find" and nothing in "Replace" to remove all spaces in Col A
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
raav05 
Register To Reply
Bookmarks