Matching two columns and pulling most common data from a third column?

[aware073]

Hey all, I am trying to come up with a formula to pull the data that matches from column A in this sheet..

Excel 2010
	A	B	C	D	E	F
1	Material	Plant	Material Description	Ext. Material Group	Storage Bin	Valuated UnRes Stock
2	200035	M001	SWITCH	M11	E0103BC	1.000
3	200037	M001	LOCKWASHER, M20 EXTERNAL	L19	F0106DB	1.000
4	200040	M001	SEAL, WIRE (U/C# 187277) 0002697-0009	M13	D0803AC	30.000
AEC

To all the same entries in column E in this sheet, and return the most common answer from column H. Basically I need to know which GL account (Sheet 2, H) the material number in sheet 1 column A is issued out as most frequently. Any suggestions??

Excel 2010
	A	B	C	D	E	F	G	H
1	Co Code	Plant	MoveType	Mat. Doc.	Material	Material Desc.	G/L Acct	G/L Acct Name
2	8100	M001	201	4900399047	210683	(R)CONTROLLER	724150	Maint-Supplies
3	8100	M001	201	4900399047	214879	SPRING, LEAF THREE	724150	Maint-Supplies
4	8100	M001	201	4900399181	214663	BEARING,PILLOW BLOCK, 10 IN. SAF056KAX10	723180	Belt-Belt Drives
5	8100	M001	201	4900399545	203407	U-JOINT	724150	Maint-Supplies
MVMTS

 

[Eric W]

I haven't been able to come up with a 1-cell formula, but if you're willing to use a helper column, I have a possibility:

In the first sheet, add this formula to G2:
=VLOOKUP(A2,Sheet2!$E$2:$H$100,4,FALSE)
and copy it down the G column as far as your data goes. The $E$2:$H$100 should match the size of your table on the second sheet. You can now hide the G column if you want.

Then add this formula to H2:
=INDEX($G$2:$G$100,MODE(MATCH($G$2:$G$100,$G$2:$G$100,0)))
That's an array formula, so enter it with Control-Shift-Enter.

And that should get you the result you want. Let me know how it works.

 

[aware073]

Still just getting all #N/A's in everything but like the first 20 rows of G with that formula.. I'm clueless here

I haven't been able to come up with a 1-cell formula, but if you're willing to use a helper column, I have a possibility:

In the first sheet, add this formula to G2:
=VLOOKUP(A2,Sheet2!$E$2:$H$100,4,FALSE)
and copy it down the G column as far as your data goes. The $E$2:$H$100 should match the size of your table on the second sheet. You can now hide the G column if you want.

Then add this formula to H2:
=INDEX($G$2:$G$100,MODE(MATCH($G$2:$G$100,$G$2:$G$100,0)))
That's an array formula, so enter it with Control-Shift-Enter.

And that should get you the result you want. Let me know how it works.

 

[Eric W]

You could get a #N/A if the number you're trying to look up isn't in the table. Look on one of the #N/A rows, check the material number, and see if it exists in the second table. If not, that's your problem. If it does, look at the row number where it matches on the second sheet. Is that row number greater than the range you put in your formula? I used Sheet2!$E$2:$H$100, but maybe your data goes to Sheet2!$E$2:$H$1000?

 

[aware073]

That was it for formula 1 sorry I didn't realize the formula only went to 100! Thanks. The second formula is still only returned #N/A's the whole column. Can you explain to me what each formula is doing so I can see if I can see the problem?

 

[Eric W]

Formula 1 is just a VLOOKUP. It matches the value in the A column in the first table with the value in the E column in the second table and returns the value in the same row 4 columns over, which is the GL Acct Name from column H. Those should be easy enough to validate.

Formula 2 is an array formula, which means it works on an array of values all at the same time. It belongs in H2, but do NOT copy it down the column. Make sure that you use Control-Shift-Enter when you type it in. If you do it correctly, you will see braces around the formula, like this:
{=INDEX($G$2:$G$500,MODE(MATCH($G$2:$G$500,$G$2:$G$500,0)))}

Again, make sure that the range ($G$2:$G$500) matches the number of rows in your first table.

How it works is kind of tricky. Let's say you have 4 rows like this:

G2 Maint-Supplies
G3 Accts Receivable
G4 Maint-Supplies
G5 Maint-Supplies

When you do a MATCH function, it returns the first item in the list that matches. So if you compare the value of G2 (Maint-Supplies) with that list, it returns 1 (first item). If you compare the value of G3, it returns 2. If you compare the value of G4, it returns 1 again. Same for G5. That's how the array part of the functions works, it does the whole list at one time. It will return an array with these values {1,2,1,1}. That's where the MODE function comes in. That returns the value in a list that occurs most often, in this case 1. And finally, the INDEX looks at a list and returns the nth item, in this case the item number is 1, and the 1st item is Maint-Supplies.

I know, it's kind of a long way around, but you have to work with the functions that Excel provides. It's possible that someone else can come up with a better formula, it's fascinating sometimes how different functions can be combined, and there's a lot I haven't seen yet.

 

[aware073]

Alright that makes sense but then shouldn't that formula in H be referencing page 2 somewhere?

Formula 1 is just a VLOOKUP. It matches the value in the A column in the first table with the value in the E column in the second table and returns the value in the same row 4 columns over, which is the GL Acct Name from column H. Those should be easy enough to validate.

Formula 2 is an array formula, which means it works on an array of values all at the same time. It belongs in H2, but do NOT copy it down the column. Make sure that you use Control-Shift-Enter when you type it in. If you do it correctly, you will see braces around the formula, like this:
{=INDEX($G$2:$G$500,MODE(MATCH($G$2:$G$500,$G$2:$G$500,0)))}

Again, make sure that the range ($G$2:$G$500) matches the number of rows in your first table.

How it works is kind of tricky. Let's say you have 4 rows like this:

G2 Maint-Supplies
G3 Accts Receivable
G4 Maint-Supplies
G5 Maint-Supplies

When you do a MATCH function, it returns the first item in the list that matches. So if you compare the value of G2 (Maint-Supplies) with that list, it returns 1 (first item). If you compare the value of G3, it returns 2. If you compare the value of G4, it returns 1 again. Same for G5. That's how the array part of the functions works, it does the whole list at one time. It will return an array with these values {1,2,1,1}. That's where the MODE function comes in. That returns the value in a list that occurs most often, in this case 1. And finally, the INDEX looks at a list and returns the nth item, in this case the item number is 1, and the 1st item is Maint-Supplies.

I know, it's kind of a long way around, but you have to work with the functions that Excel provides. It's possible that someone else can come up with a better formula, it's fascinating sometimes how different functions can be combined, and there's a lot I haven't seen yet.

 

[Eric W]

Nope. The formula in H is referencing the G column, which is referencing the second sheet. So indirectly, it is referencing the second sheet.

Of course, I could be misunderstanding what you're asking for, in which case, all bets are off! But try this formula first and see what you get.

 

[aware073]

Let me see if I can explain this better.
Page one is just a list of materials with other irrelevant data. Page two is a list of those materials every time they've been "issued out" of the computer system, and what GL account they were issued to. (Therefore, each material will be listed multiple times, as many times as that type of material was been issued). I need the list of materials on page one to have a column that shows which GL account they are issued out to most frequently.

 

[Eric W]

Ah, I see now. I was giving you a way to find the most common GL account for the entire material list, not item by item. It's actually a bit easier to do item by item. Clear out your G and H columns. Put this array formula in G2 (Control-Shift-Enter), and copy it down:

=INDEX(Sheet2!$H$2:$H$500,MODE(IF(A2=Sheet2!$E$2:$E500,MATCH(Sheet2!$H$2:$H$500,Sheet2!$H$2:$H$500,0),"")))

This formula will return a #N/A error if the material number is not found in the second table, or if it is found, but no GL account is used more than once. If that's an issue, I can add a VLOOKUP to return the first GL account found.

Let me know how that works.