Solved: VLOOKUP or some other way

[austenr]

Have not posted a question for awhile but need some help on this. I need to match two worksheets but in a more difficult way than I have ever tried before. Instead of taking up a lot of room explaining, I have attached a workbook sample as an explaination. I have done this sort of thing before using conditional formatting but do not think it will work on this because you have to match additional rows based on if you find a match on the row above. I hope the attached spreadsheet will answer most of the questions as I have provided examples. Thanks http://vbaexpress.com/forum/images/smilies/eek.gif

[geekgirlau]

Have a look at the sample attached to get an idea of using a combination of MATCH and INDEX to find out whether the values are the same. I haven't looked at coding a solution for this as yet, as I'm not totally clear on the layout of the actual data and how closely it will match your sample.

[austenr]

I reworked my sample workbook to make it easier to see what I am looking for.

[austenr]

Hi Geekgirl,

Not real sure where you are going with that. My intent was to only highlight each cell that did not meet the exact match crireria. Looks like all of the data is blue on the right side of your example. Could you reiterate how your example works. Thanks http://vbaexpress.com/forum/images/smilies/102.gif

[geekgirlau]

Sorry, I should have explained the logic here ... :doh:

The blue highlighted area are formulas - I would envisage that these columns would end up hidden, but I've left them visible here so you can see the formulas used. The data uses a conditional format that highlights the cell in yellow if the value does not match the lookup value in the "hidden" columns.

[austenr]

Looks promising geekgirl. http://vbaexpress.com/forum/images/smilies/023.gif One other thing...If I have to add columns can I copy and paste the formulas? http://vbaexpress.com/forum/images/smilies/102.gif

[austenr]

OK. I had geekgirls' solution working for me but now I find it necessary to split the comparison into seperate sheets. Since you cannot reference cells on a different sheet when using conditional formatting, is there another approach? Kind of under the gun on this one guys. Thanks

[austenr]

Let me see if I can explain more completely what I am trying to do:



What I am trying to do is cross match two worksheets.



Worksheet 1:



Find the first value in column A and find a MATCH to that cell anywhere in column A on Sheet2. If not highlight that cell along with the rest of the cells in that row along with the cells in the rows directly under that row until you get to the next value in Column A. This means that you did not find any entries that matched them on sheet2.



If you have a MATCH, then check the rest of the cells on that row against the corresponding row on Sheet2. If any are not exactly alike, highlight. Now check the rows immediately under the matched row to see if there is a corresponding row on sheet2. If any are not alike or missing on sheet2, highlight them on sheet1.



Continue checking until you get to the next value in column A on sheet1 and repeat the process.



Of course reverse the checking from sheet2 to sheet1 as above to complete the cross check.



I thought conditional formatting would work on this but I cannot get it to work. I will have a total of 19 columns to match. If there is an easier way to do this without formulas I would be thrilled to know it. Thanks in advance for your help.

There is a partial example of the data in geekgirls reply above. Her solution would work but when I add the extra columns I cannot seem to get it to work anymore. :(

[geekgirlau]

The difficulty here is the first column. Is the first column a unique identifier in each case? From your sample, it looks like the first value in each "group" (as you have separated them out currently) has a unique ID, but then the remainder in each group is not unique. Is this the case?

If the first column consists of all unique values, you can use the first MATCH formula in cell D1 all the way down the column. If not, we're going to need a completely different solution.

With regards to the conditional formatting, you're right, it only works on the same sheet. However even with 19 columns to compare rather than the 3 in your sample, the concept is still the same.

The first hidden column (D in my sample file) simply tells you which row the ID was found in on sheet 2. The remaining columns (E to G) use INDEX to find the corresponding values in each column on that row. We need to display the values in each column from sheet 2 so that the conditional formatting can work (although as I mentioned before you will end up hiding these formula columns once everything is ready to go.

If you insert extra columns after C, the formulas will be fine, but you need to adjust the conditional formatting. If you look at the conditional formatting for A1 for example, it highlights the cell in yellow if the value does not match E1. Once you insert your additional columns you will need to change E1 to the relevant cell.

I hope this makes sense - my only concern here is that first column.

[austenr]

OK. You are correct in that the first column is indeed the unique identifier. That said, and looking at your response, I would ask if you could make up a sample workbook for me so I can see exactly what s happening. It is things like this that are hard for me to grasp (complex formulas) but I am trying to learn. If you wanted to just do maybe 10 rows that would be fine as I could copy and paste from there. Thanks for all of your work and help. http://vbaexpress.com/forum/images/smilies/039.gif

[geekgirlau]

Give this a try - it still only has three columns, but the formulas will allow you to insert as many additional ones before column E as you need.

[austenr]

Well I had this working I thought. The problem lies in the first column where after the row that has a number you have rows related to the row above with the number. I need a way to match those rows to Sheet2. Geekgirls solution works fine if you have a number in every cell in column A. I would not have a problem if there was something in the cells under the first number in column A such as DEP 1, DEP 2, etc. Would that work using her example as a guide? I am attaching a sample workbook to show what I mean. http://vbaexpress.com/forum/images/smilies/100.gif

[geekgirlau]

Okay, have a look at the new attachment.

Again, we're assuming that the blue columns are for the purpose of calculating only, and will end up hidden.

Cell H1 uses MATCH to find the row in which the matching identifier from column A can be found on Sheet 2. However because the identifer doesn't appear in every row, the formula in cell H2 is slightly different. If column A is blank, then add one to the value of the cell above (i.e. assume it's the next row down), otherwise use MATCH.

The remaining columns use INDEX to capture the value from Sheet 2 in the row number identified in H, in a specific column.

The data in columns A to F have a conditional format that compares the value in the cell to the value in columns I to N respectively - if the value's don't match, colour the cell yellow.

Sheet2 is identical (except that obviously the formulas refer to Sheet1).

Let me know how you go with this one.