How to compare two lists to identify what's new and allow for blank cells?

[rizzo93]

I have two similarly formatted lists on different worksheets in the same workbook.

List A is my master list. List B, which changes weekly, has some values that exist on List A along with completely new values.

Each list has about 2,300 rows. I must compare these two lists and find the new values on List B. Ordinarily, I could find the changes if the data on every row had a value across multiple columns. But these lists are like the example below:

List A

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Grandparent
[/TD]
[TD]Parent
[/TD]
[TD]Child
[/TD]
[/TR]
[TR]
[TD]Region 1
[/TD]
[TD]Department 1
[/TD]
[TD]apple
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]orange
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]mickey
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Minnie
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Department 2
[/TD]
[TD]clark
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]bruce
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]mary
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]louise
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Department 3
[/TD]
[TD]george
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]rick
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]sally
[/TD]
[/TR]
[TR]
[TD]Region 2
[/TD]
[TD]Department 4
[/TD]
[TD]jojo
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]bob
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Robert
[/TD]
[/TR]
</tbody>[/TABLE]


List B

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Grandparent
[/TD]
[TD]Parent
[/TD]
[TD]Child
[/TD]
[/TR]
[TR]
[TD]Region 1
[/TD]
[TD]Department 1
[/TD]
[TD]apple
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]orange
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]mickey
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Minnie
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]cisco
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]david
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Department 2
[/TD]
[TD]clark
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]bruce
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]mary
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]louise
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]roger
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]stephen
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Department 3
[/TD]
[TD]jojo
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]bob
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Robert
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]marcus
[/TD]
[/TR]
</tbody>[/TABLE]

The names in red are the new values that I need to identify, but also need to know the relationship they have with Region 1 and the three Departments. So if I find that "roger" has been added, I must also know that he's under Department 2, which is under Region 1.

I welcome any input to help me figure this puzzle out. Thanks.

 

[Fluff]

Why not just fill the details down for all rows.
Ctrl G > Special > Blanks > Ok > enter =A1 (or whatever is above the active cell) > Ctrl Enter

 

[Saba Sabaratnam]

If you fill the blank with department and region as suggested by Fluff, the solution will be simpler.

If you want to keep the table format as it is, try this one.

Copy the first table from Column A to C
Copy the second table from E to G
Enter the following formula into H2 and copy it down

=IF(ISNA(VLOOKUP(G2,C:C,1,FALSE)),"New","")

Enter the following array formula into I2 by pressing Control + Shift + Enter and copy it down

=IF(H2="","",INDEX($F$2:$F$17,MATCH(VLOOKUP(ROW(),IF($F$2:$F$17<>"",ROW($F$2:$F$16),""),1,TRUE),ROW($F$2:$F$17),0)))


Enter the following array formula into J2 by pressing Control + Shift + Enter and copy it down

=IF(H2="","",INDEX($E$2:$E$17,MATCH(VLOOKUP(ROW(),IF($E$2:$E$17<>"",ROW($E$2:$E$16),""),1,TRUE),ROW($E$2:$E$17),0)))

Let me know how you go.

Kind regards

Saba

 

[rizzo93]

Thanks for the feedback, Fluff and Saba.

The structure I used in my example was over-simplified, so as weird as it may sound, filling in the cells is more complicated than it seems. There are a few reasons for this:

1. Cells like Region 1 are a merged cell.
2. Beneath that merged cell are another couple of single cells with text in them. Then another merged cell comes next for Region 2 and so on. Same thing for the column for Department 1, 2, etc.
3. Even if 1 and 2 above did not exist or even if I selected the entire column to do an unmerge, I'd still be looking at manually going through approximately 2,300 rows to fill in the data. Even using Ctrl-D for a specific group of cells would be time-consuming.

I was thinking that helper columns may be of use in this situation. Maybe checking to see when a cell has a new value, then repeating it further down until a new one comes along. Not exactly sure if that's possible, but then I'd use those columns to do my comparison with.

Sorry for any confusion. I hope this clarifies things.

 

[Saba Sabaratnam]

I modified the formula to consider only Department and Region in the formula.

Enter the following array formula into I2 by pressing Control + Shift + Enter and copy it down

=IF(I2="","",INDEX($G$2:$G$17,MATCH(VLOOKUP(ROW(),IF(ISNUMBER(SEARCH("Department*",$G$2:$G$17)),ROW($G$2:$G$16),""),1,TRUE),ROW($G$2:$G$17),0)))

Enter the following array formula into J2 by pressing Control + Shift + Enter and copy it down

=IF(I2="","",INDEX($E$2:$E$17,MATCH(VLOOKUP(ROW(),IF(ISNUMBER(SEARCH("Region*",$E$2:$E$17)),ROW($E$2:$E$16),""),1,TRUE),ROW($E$2:$E$17),0)))

I tested the formula with merged cells. It did not cause any problem.

Kind regards

Saba

 

[rizzo93]

Thank you, Saba. I'll give this a try. Much appreciated.