Matching records to a reference sheet and displaying data found

[riccardo]

Hi there, hope someone can help me with this one. It's a 2 part question and might turn out to be a novel.
Part one: I have 2 sheets. One is a reference library 3 columns wide containing information such as:
[TABLE="width: 339"]
<tbody>[TR]
[TD]Exterior[/TD]
[TD]Roof[/TD]
[TD]Cladding[/TD]
[/TR]
[TR]
[TD]Interior[/TD]
[TD]Door[/TD]
[TD]Door Closer[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Note there is a hierarchy there, from left to right.
The other is a larger data sheet with 6 relevant columns like so:

[TABLE="width: 602"]
<tbody>[TR]
[TD]Exterior[/TD]
[TD]Door[/TD]
[TD]Doorframe[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]other data[/TD]
[/TR]
[TR]
[TD]Exterior[/TD]
[TD]Roof[/TD]
[TD]Cladding[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]other data[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What I need is a formula that will search the reference sheet for an entry that matches in all three columns - if matching in all three then it will paste the 3 cells into the empty cells (or at least enter something into a seventh column) giving:

[TABLE="width: 602"]
<tbody>[TR]
[TD]Exterior[/TD]
[TD]Door[/TD]
[TD]Doorframe[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]empty[/TD]
[TD]other data[/TD]
[/TR]
[TR]
[TD]Exterior [/TD]
[TD]Roof[/TD]
[TD]Cladding[/TD]
[TD]Exterior[/TD]
[TD]Roof[/TD]
[TD]Cladding[/TD]
[TD]other data[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I can then extract the newly populated records.
Part 2: Then, I need to search the remaining data for similar matches in the reference library (like misspells) and identify them in another cell, ie search the remaining data for:

[TABLE="width: 339"]
<tbody>[TR]
[TD]Exterior[/TD]
[TD]Roof[/TD]
[TD]Roof Cladding[/TD]
[/TR]
[TR]
[TD]Exterior[/TD]
[TD]Exterior Roof[/TD]
[TD]Cladding[/TD]
[/TR]
[TR]
[TD]Exterior [/TD]
[TD]Roof[/TD]
[TD]Roof Claddig[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So I can standardise it to
[TABLE="width: 602"]
<tbody>[TR]
[TD]Exterior [/TD]
[TD]Roof[/TD]
[TD]Cladding[/TD]
[/TR]
</tbody>[/TABLE]

Told you it might be a novel. Any ideas? Thank you in advance.

Riccardo

 

[Chris Mack]

I would try a formula like this in the first sheet (in D1 then copied across/down):

=IFERROR(INDIRECT("Sheet2!"&ADDRESS(MIN(IF(Sheet2!$A$1:$A$10=$A1,IF(Sheet2!$B$1:$B$10=$B1,IF(Sheet2!$C$1:$C$10=$C1,ROW($A$1:$A$10))))),COLUMN())),"Not found")

Ranges can be altered to suit.

As for Part 2, if possible, I would create a list of acceptable terms for each level/column, then do a conditional formatting reference against them.

E.g., if your lists were in 'Sheet3!A1:C10', the rule for the first level would be:

=COUNTIF(Sheet3!A:A,A1)=0

(Applied to "=Sheet2!$A:$A".)

Then you could amend this for the other columns.

Hope this helps,

Chris.