IF + AND (or vlookup formula?)

[dangorka]

Hi guys,


I need a help with my spreadsheet


I have data populated by scraping VBA macro in range J2:S9


Table in range A1:G9 should automatically "process" with formulas data according to my needs. Each row represents separate game. Under each game there will be either one (the same) or two (different) lines available


For example for third row (game id 2) line is the same (0.5). Therefore cell B3 should display this line (0.5) and in next cell (C3) display the highest value from respective columns =MAX(K3, N3, Q3). Cell D3 should do the same but for an away team so =MAX(L3, O3, R3) (in this case for cell C2 correct number to display is 0.93 and for cell D2 it's 1). Cells E2, F2 and G2 in this scenario should be auto-populated with "n/a" text.


However it gets tricky for rows like second row (game id 1). In this case line A is the same as line B but different than line C, therefore for each line maximum values have to be treated separately (formula must also deal with similar situations when for example Line A is the same as line C but different than line B) therefore cell B2 should display one unique line (0/0.5) cell C2 and D2 should display the highest respective value (so either K2 or N20 for C2 and L2 or O2 for D2). Then another line should be auto-populated by formula in cell E2 (0.5 from cell P2) and therefore F2 will be simply a value from cell R2 and H2=R2


I hope it does make sense, attaching photo of the sheet below (as a new user I can't attach files it seems)

 

[Peter_SSs]

Welcome to the MrExcel board!

(as a new user I can't attach files it seems)

Nobody can attach files but you can post screen shots that data can be copied from (like mine here) which will more likely attract helpers as we then don't have to manually type data. See my signature block below for further details.

Do these formulas, copied down, do what you want?
If not please post sample data with expected results & any further explanation.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Game id	First line	Home 1st line	Away 1st line	Second line	Home 2nd line	Away 2nd line			Line A	Home A	Away A	Line B	Home B	Away B	Line C	Home C	Away C	Game id
2	1	0/0.5	0.85	1.11	0.5	1.07	0.83			0/0.5	0.82	1.11	0/0.5	0.85	1.08	0.5	1.07	0.83	1
3	2	0.5	0.93	1						0.5	0.92	1	0.5	0.93	0.99	0.5	0.92	0.98	2
4	3	0/0.5	0.86	1.11						0/0.5	0.82	1.11	0/0.5	0.86	1.06	0/0.5	0.82	1.06	3
5	4	0/0.5	0.74	1.26						0/0.5	0.71	1.26	0/0.5	0.74	1.22	0/0.5	0.7	1.21	4
6	5	0.5	0.87	1.07	-0.5	0.84	1.04			0.5	0.85	1.07	0.5	0.87	1.05	-0.5	0.84	1.04	5
7	6	0/0.5	1.09	0.85						0/0.5	1.07	0.85	0/0.5	1.09	0.84	0/0.5	1.06	0.82	6
8	7	0/-0.5	0.71	1.28						0/-0.5	0.71	1.26	0/-0.5	0.7	1.28	0/-0.5	0.68	1.25	7
9	8	0/-0.5	0.83	1.1						0/-0.5	0.83	1.09	0/-0.5	0.83	1.1	0/-0.5	0.81	1.07	8
dangorka

Cell Formulas
Range	Formula
B2	=J2
C2	=AGGREGATE(14,6,K2:Q2/((LEFT($K$1:$Q$1,4)="Home")*(J2:P2=$B2)),1)
D2	=AGGREGATE(14,6,L2:R2/((LEFT($L$1:$R$1,4)="Away")*(J2:P2=$B2)),1)
E2	=IF(M2=B2,IF(P2=B2,"",P2),M2)
F2	=IF(E2="","",AGGREGATE(14,6,K2:Q2/((LEFT($K$1:$Q$1,4)="Home")*(J2:P2=$E2)),1))
G2	=IF(E2="","",AGGREGATE(14,6,L2:R2/((LEFT($L$1:$R$1,4)="Away")*(J2:P2=$E2)),1))

 

[dangorka]

Yes! That's exactly what I needed, many, many thanks for such a quick response! interesting formula I didn't have a clue about before!

 

[Peter_SSs]

Yes! That's exactly what I needed, many, many thanks for such a quick response! interesting formula I didn't have a clue about before!

You are very welcome. Glad it helped.