Pivot table or function?

[jod227]

Hello all,

I have data in 2 separate workbooks. In the first one I have data that includes a column that contains textual data that matches with (in different order) a column in the other workbook.

I have a number score in a column that's next to the text column in the 1st workbook (ex. 1-100) and a text score next to the text column in the 2nd workbook (ex. Yes, no, maybe). I ultimately need to see the scores next to each other based on the matching text in workbook 1.

Would this require an advanced pivot table of some sort? Any insight on this would be greatly appreciated!


Workbook 1 **what outcome would be**

[TABLE="width: 500"]
<tbody>[TR]
[TD]white[/TD]
[TD]50.8[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]blue[/TD]
[TD]6.2[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]green[/TD]
[TD]70.3[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]red[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Workbook 2

[TABLE="width: 500"]
<tbody>[TR]
[TD]yellow[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]green.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]green[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]red[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]white[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]blue[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]blue.0[/TD]
[TD]maybe[/TD]
[/TR]
</tbody>[/TABLE]

(Workbook 2 has a few thousand more rows than 1 but all of workbook 1's textual data is contained within the larger column in Workbook 2 - I only care about the text in Workbook 1)

 

[oldbrewer]

[TABLE="width: 704"]
<colgroup><col span="11"></colgroup><tbody>[TR]
[TD="colspan: 4"]Workbook 1 **what outcome would be**[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]white[/TD]
[TD="align: right"]50.8[/TD]
[TD]yes[/TD]
[TD]<<<<<<[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]blue[/TD]
[TD="align: right"]6.2[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]green[/TD]
[TD="align: right"]70.3[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]red[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Workbook 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]yellow[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]green.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]green[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]red[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]white[/TD]
[TD]yes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]blue[/TD]
[TD]no[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]blue.0[/TD]
[TD]maybe[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]formula in cell marked <<<<<[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 9"]=IF(ISERROR(MATCH(A3,$A$10:$A$16,0)),"",OFFSET($A$9,MATCH(A3,$A$10:$A$16,0),1))[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jod227]

Brilliant! This is exactly what I need, thanks oldbrewer!

 

[oldbrewer]

your homework is to get rid of the zero against red in top table..........

 

[jod227]

I basically just highlighted the column and did a find and replace but I didn't figure out how to do it in the formula.

 

[oldbrewer]

[TABLE="class: cms_table, width: 704"]
<tbody>[TR]
[TD="colspan: 9"]=IF(ISERROR(MATCH(A3,$A$10:$A$16,0)),"",OFFSET($A$9,MATCH(A3,$A$10:$A$16,0),1))
becomes
[TABLE="class: cms_table, width: 704"]
<tbody>[TR]
[TD="colspan: 9"]=IF(ISERROR(MATCH(A3,$A$10:$A$16,0)),"",if(OFFSET($A$9,MATCH(A3,$A$10:$A$16,0),1)="","",OFFSET($A$9,MATCH(A3,$A$10:$A$16,0),1))

may not have right number of brackets as I have not checked it

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]