comparing two columns under specific condition- please help!

**flutterby** · 06-01-2009, 09:02 PM

I have list a of all the company account numbers, and list b of the company account's corresponding industry number.

Then I have list c which is a specific list of company account numbers that are from list a (just a more specific list). Then I have list d of the corresponding industry numbers for list c.

I need to check that list c has the correct corresponding industry numbers.

SO I have to compare list b to list d, however only when the same number appears in list a and list c.

HELP!!

**JBeaucaire** · 06-01-2009, 09:33 PM

Hi Flutterby. Welcome to the forum.

Can you mock up a sample workbook showing a good sampling of data and how the "comparison" is supposed to get its values. Also, show us before/after what you want to happen when errors are found.

Are you wanting a macro to "fix" the original data somehow, or some formula to flag the rows for you to review?

Click on GO ADVANCED and use the paperclip icon to post your sample workbook.

**flutterby** · 06-02-2009, 12:22 PM

column a
2
5
6
7
8
9
12
15

column b
list of number 1-45

column c
100
120
133
144
154
160
172
178
(corresponding to numbers in column a)

column d
list of corresponding numbers from the 1-45 list that have the same meaning as the nmbers in column d, but is the complete data set

So example of what I have to do:
For the first value of 2, I look up the corresponding value found in column c... then i find 2 on the larger list of numbers (column b) and see the corresponding number which is found in column d. Then i compare column b to d to see if they are the same..

**JBeaucaire** · 06-02-2009, 12:30 PM

That doesn't really qualify as a sample workbook, so I'll post a suggested formula and let you test it yourself rather than try to create a workbook you could have just uploaded.

You also failed to demonstrate where this "comparison" is occurring, so I'll suggest column E.

This is a truly odd layout out you've demonstrated. It would be more common to have the chart of numbers 1-45 and adjacent values in a table somewhere, then the values you're checking with it's adjacent values, too.

Anyway. Try this:
=IF(C1=INDEX(D:D,MATCH(A1,B:B,0)),"Match","Not A Match")

**flutterby** · 06-02-2009, 01:42 PM

I received an N/A error....

column c and d have duplicate numbers because the numbers in these codes indicate the industry codes.. so there are a few companies within the same industry, which may be causing the problem.....

**flutterby** · 06-02-2009, 01:48 PM

Actually some of them came up with the match and do not match. Maybe I'm not explaining what I have to do well enough.

I have a list of a select number of companies. Each company is then assigned another number according to its industry. however, some of the industry numbers are changed and soemtimes they are changed periodically. So I want to come up with a formula/macro that will check for any discrepancies with the list of specific companies I am dealing with, and compare them against the master list of companies and their industry codes. So to do this I would normall have to look up the company number on the full list and check its industry code then compare it to the industry code I have on my list....

**flutterby** · 06-02-2009, 02:05 PM

actually I think it works!

Is there any way to be able to replace the wrong code number with the correct code number?

**JBeaucaire** · 06-02-2009, 06:17 PM

In post #2 I asked for a sample workbook to be posted. I think now is a good time to resubmit that request. Not just a sample of data, but samples of your desired results, too.

So, a little before/after treatment here looking right at the data will help us speed this along immensely.