Please Help with comparing columns!

Closed
shay - Oct 22, 2010 at 10:41 AM
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 - Oct 26, 2010 at 03:17 PM
Hello,

I have an excel worksheet that has two columns of information that I need to compare. For example, If any of the values in column b = a value in column a then I need it to mark an "x" in column c. If there are no matches then the cell in column c should be left blank.

I hope that makes sense.

5 replies

in column c:
=IF(A1=B1,"x","")
0
I tried the suggested solution, it didn't work. Let me be more specific. I have one worksheet that has multiple columns. The data I have to compare is in two different columns, (I have to compare the list in column D with the list in column A), if the value in column D matches a value in column A then I need an "x" placed in column C.

Here is an example of the columns:

A B C D
C5000 733.436-64 c5000
C5010 733.436-64 c5001
C5020 733.436-64 c5011
C5040 733.436-64 c5021
C5050 733.436-64 c5031
C5060 733.436-64 c5071
C5070 733.436-64 c5081
C5071 733.436-19 c5091
C5080 733.436-64 c5101

I've tried an if- match formula, and index - match formula, I don't think vlookup would work..I'm sure this can be done, I'm just not having any luck with the formulas I've tried.
0
the columns won't necessarily correspond to an adjacent cell
0
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 25
Oct 25, 2010 at 03:29 PM
Why do you think vlookup wont work?
In column c:
=IF(ISNA(VLOOKUP(D1,A:A,1,FALSE)),"",VLOOKUP(D1,A:A,1,FALSE))

This matches the 2 hits in your example and takes care of the #N/A if no match is found.
0
When I use this I get a "false-positive". Meaning, there is an "x" in a cell that does not have a corresponding match. At first it appears as if the code is working. Then I drag the formula down and it's returning incorrect data.

I didn't think vlookup would work because of the information being alphanumeric and not being consistent in case usage. Also, it seems to me that when I use a "1" for the index number it keeps reverting to returning the value for the uppermost left column.
0
Also, I tried: IF(ISNA(A2,$D$2:$D$9,1,FALSE)),"","X") - but I have the same issue. When I walked through it step by step in excel the first result was correct, but none of the rest are.
0
shay06 Posts 1 Registration date Tuesday October 26, 2010 Status Member Last seen October 26, 2010
Oct 26, 2010 at 12:20 PM
Also, when researching vlookup and how it works it states that for table_array I have to use two or more columns of data sorted in ascending order. I only have one column of data for my table_array. Could that be what's causing it not to work properly?
0
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 25
Oct 26, 2010 at 03:17 PM
This doesn't syntax check. IF(ISNA(A2,$D$2:$D$9,1,FALSE)),"","X")

The solution I gave works by matching the value in column D agaisnt all values in column A. I used 1 to return the first column seeing as there is only one column in the search.
C5000 and C5071 are found.
Are you dragging the formula down through all the rows? i.e. row 2 should be D2 row 3 D3 etc. Maybe we need more data to test with.
0

Didn't find the answer you are looking for?

Ask a question
Blocked Profile
Oct 22, 2010 at 03:24 PM
Hi there,

Please have the problem solved by getting through the below instructions.

https://ccm.net/faq/5278-how-to-compare-two-workbooks-worksheets

Thank you.
-1
I tried the suggested solution, it didn't work. Let me be more specific. I have one worksheet that has multiple columns. The data I have to compare is in two different columns, (I have to compare the list in column D with the list in column A), if the value in column D matches a value in column A then I need an "x" placed in column C.

Here is an example of the columns:

A B C D
C5000 733.436-64 c5000
C5010 733.436-64 c5001
C5020 733.436-64 c5011
C5040 733.436-64 c5021
C5050 733.436-64 c5031
C5060 733.436-64 c5071
C5070 733.436-64 c5081
C5071 733.436-19 c5091
C5080 733.436-64 c5101


I've tried an if- match formula, and index - match formula, I don't think vlookup would work..I'm sure this can be done, I'm just not having any luck with the formulas I've tried.
0
for clarification, since the data above squished together, A coressponds to the C5000 column, B corresponds to the 733.436-64 column, the C column is empty, and D corresponds to the c5000 column.

Thank you!
0