If contains "text string" then return highest value from key (advanced)

[Batvatar]

Hello all,

I have an advanced "if then" question, thank you in advanced for any help..

I have 45,000 accounts, some duplicates that had 1 piece of equipment per account... I already concatenated the information to give me 20,000 UNIQUE accounts with a cell of multiple pieces of equipment separated by commas... on Tab 2 I have a list of different pieces of equipment that have a value assigned to each piece of equipment...

What I need is a formula that will do the following... look for a piece of equipment that is on tab 2 in the concatenated information, and bring back the value of THE HIGHEST piece of equipment that is labeled on tab 2.. The trick here is that the concatenated information will have multiple matches to the equipment list on tab 2... but the values are all different.. so I need it to look at the table, and find the equipment with the HIGHEST value from tab that is matched int the concatenated info.. then I need to be able to copy that down about 20,000 times...

thanks again everyone!

Example:

Tab 2: the Key

[TABLE="width: 206"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]Account 1
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]Account 2
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]Account 3
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]Account 4
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]Account 5
[/TD]
[TD]8
[/TD]
[/TR]
</tbody>[/TABLE]



Tab 1I'm trying to solve for "C")

[TABLE="width: 457"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Account 1
[/TD]
[TD]machine 1, machine 2, machine 3
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Account 2
[/TD]
[TD]machine 1, machine 2, machine 5
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Account 3
[/TD]
[TD]Machine 1, machine 3
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]Account 4
[/TD]
[TD]machine 3, machine 5
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]Account 5
[/TD]
[TD]machine 5
[/TD]
[TD]8
[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

This is so much easier if you don't concatenate

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F
1	Account 1	machine 1	machine 2	machine 3		12
2	Account 2	machine 1	machine 2	machine 5		12
3	Account 3	Machine 1	machine 3			12
4	Account 4	machine 3	machine 5			8
5	Account 5	machine 5				8
6
7
8
9	Machine 1	12
10	Machine 2	6
11	Machine 3	7
12	Machine 4	9
13	Machine 5	8
Sheet14

Cell Formulas
Range	Formula
F1	{=MAX($B$9:$B$13*COUNTIF($B1:$D1,$A$9:$A$13))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Batvatar]

I don't have to, I still have the original, with each piece next to each account, and duplicate account. I have a separate formula that also looks to the unique identifier of each account, and tells me in the last column if it is the last on the list.. so I can sort by that...

can we do a formula that assigns the value of the equipment in 1 line, then I can use a formula to grab the highest value on each duplicate account and apply it to the last listing of that account... then I can use my formula that assigns a "last" label to the last listing of every account?

so it may look like this? then I can just sort by my already comleted "LAST" formula, to get back to my original 20k accounts, but with a new column that has the highest value?


[TABLE="width: 885"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD](val from key)[/TD]
[TD](Last y/n)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Account 1[/TD]
[TD]machine 1[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]account 1[/TD]
[TD]machine 2[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]account 1[/TD]
[TD]machine 5[/TD]
[TD]8[/TD]
[TD]12[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Account 2[/TD]
[TD]machine 1[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]account 2[/TD]
[TD]machine 2[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]account 2[/TD]
[TD]machine 5[/TD]
[TD]8[/TD]
[TD]12[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]account 3[/TD]
[TD]machine 1[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]account 3[/TD]
[TD]machine 3[/TD]
[TD]7[/TD]
[TD]12[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]account 4[/TD]
[TD]machine 3[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]account 4[/TD]
[TD]machine 5[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]account 5[/TD]
[TD]machine 5[/TD]
[TD]8[/TD]
[TD]8[/TD]
[TD]yes[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col span="2"></colgroup>[/TABLE]

 

[Batvatar]

my table is set up where the accounts and their duplicate are in A1,A2,A3, etc, and the equipment is B1,B2, Etc.. (See below) or would you recommend using the formula as you have it outline here? I would think I would need another formula to take the account duplicates and dispurse the equipment accross B1,C1,D1 etc?

 

[sheetspread]

In the key shouldn't "account" read "machine"? I think this is an easy problem to solve but your data sample seems distorted.

 

[Batvatar]

In the key shouldn't "account" read "machine"? I think this is an easy problem to solve but your data sample seems distorted.

No it's correct, column A holds all of the accounts, call column B holds each accounts type of equipment. If there is a duplicate account in column A, it is because it is the same account but with multiple different types of equipment. So account "joes" can be listed 20 times with 20 pieces of equipment. The "Key" is on a different tab, and is just every type of equipment I have in A, and, the value of it in B.

Does that help?

 

[sheetspread]

In the key Account 1 is 12, Account 2 is 6, yet in the table in post #4 Account 1 Machine 1 is 12, Account 2 Machine 1 is 12. How are you determining this?

 

[Batvatar]

you are correct, sorry that is my fault, my key should read as follows; sorry for the confusion!

Tab 2: the Key

[TABLE="class: cms_table, width: 206"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]machine 1[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]machine 2[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]machine 3[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]machine 4[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]machine 5[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

 

[sheetspread]

Ok that helps a little, but why is D9 8 in my table but 7 in yours?

Excel 2010
	A	B	C	D
1	Account 1	machine 1	12	12
2	account 1	machine 2	6	12
3	account 1	machine 5	8	12
4	Account 2	machine 1	12	12
5	account 2	machine 2	6	12
6	account 2	machine 5	8	12
7	account 3	machine 1	12	12
8	account 3	machine 3	7	12
9	account 4	machine 3	7	8
10	account 4	machine 5	8	8
11	account 5	machine 5	8	8
12
13
14
15	machine 1	12
16	machine 2	6
17	machine 3	7
18	machine 4	9
19	machine 5	8
Sheet15

Cell Formulas
Range	Formula
C1	=VLOOKUP(B1,$A$15:$B$19,2,0)
D1	{=MAX(IF($A$1:$A$11=A1,$C$1:$C$11))}
Press CTRL+SHIFT+ENTER to enter array formulas.