[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Finding matches between tables with columns and rows transposed

Posted on 2011-10-14
8
Medium Priority
?
197 Views
Last Modified: 2012-08-14
I have two tables:

Table 1 has serial numbers across the top, and dates down the leftmost column
Table 2 has dates across the top, and serial numbers down the leftmost column

I need formulas in Table 2 that will look for matches in the 'transposed' intersections in Table 1.

Please look at the attached workbook to see what I mean. The solution needs to work in Excel 2003 as well as 2007.

Thanks,
John
Find-Intersections.xlsx
0
Comment
Question by:gabrielPennyback
  • 4
  • 3
8 Comments
 
LVL 42

Accepted Solution

by:
dlmille earned 1000 total points
ID: 36971856
Put this formula in cell E19, then copy down and across:

=SUMPRODUCT(($C$3:$C$14=E$18)*($D$2:$F$2=$C19)*($D$3:$F$14))

See attached...

Dave
Find-Intersections-1-.xlsx
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36971859
To explain, the first parameter:

($C$3:$C$14=E$18) locks in a ROW based on the month

The second Parameter:

($D$2:$F$2=$C19) locks in the COLUMN based on the serial number

The third Parameter:

($D$3:$F$14) is the data to sum, based on the locked ROW and COLUMN intersection

Cheers,

Dave
0
 
LVL 50

Assisted Solution

by:barry houdini
barry houdini earned 1000 total points
ID: 36971879
Hello John,

Normally to return a single value it's best to use a "lookup" type function in my opinion. My preference would be this formula in E19 copied across and down

=INDEX($D$3:$F$14,MATCH(E$18,$C$3:$C$14,0),MATCH($C19,$D$2:$F$2,0))

or you could use HLOOKUP or VLOOKUP if you want it shorter, i.e.

=HLOOKUP($C19,$D$2:$F$14,MATCH(E$18,$C$3:$C$14,0)+1,0)

These will both work with either numeric or text data unlike Dave's SUMPRODUCT suggestion

See attached

regards, barry
27397976.xls
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 42

Expert Comment

by:dlmille
ID: 36971909
IMHO - The sumproduct is easier to understand, espeically when doing multiple backflips to find an intersection, rather than pull out INDEX, MATCH, and H/VLOOKUPS.  The data is numeric, ergo :)

However, its good for all of us to know multiple ways to skin the cat, as the knife can get rusty :)

0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36972929
Hello Dave/John,

Of course both methods are valid here and Dave's SUMPRODUCT method would be the better option if there could be multiple matches in either row or column headers (with the aim to sum all the intersection values for those matches).

I'd take issue with your comment about understanding, though. I think the concept of the INDEX/MATCH/MATCH setup is quite simple.

INDEX works like this

=INDEX(Area,x,y)

will give you the value at the intersection of the xth row and the yth column of Area, so, for example, if you have

=INDEX(D3:F7,3,2)

that will give you the value from the 3rd row of D3:F7 (row 5) and the 2nd column (column E) so it gives the value from E5

In the above formula the x and y values are provided by MATCH.  That function finds the position of the first instance of a value in a vector (a single column or row). So if you use

=MATCH("abc",F3:J3,0)

and abc is found first in H3 then the formula returns 3 because H3 is the 3rd cell in the F3:J3 row

Of course it doesn't make much difference in a small table but INDEX/MATCH/MATCH would also be more efficient than multiplying matrices/arrays in SUMPRODUCT

regards, barry
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 36972936
This link (Example 2) is an example of the method I suggested

barry
0
 
LVL 42

Expert Comment

by:dlmille
ID: 36972954
You go, barry!

:)
0
 
LVL 1

Author Closing Comment

by:gabrielPennyback
ID: 36982361
You guys are amazing, thanks! Dave's answer is perfect since we're looking for numbers in this case.

But of course Barry's answer accommodates text as well. It took me a while to understand it, but if I have it right its logic simply finds the correct x and y values, i.e. finds the intersection.

Thank you both.

- John
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question