Compare + Find + Write Equation

[mtjanousek]

Hello,
I got stuck with my computation. I will try to explain the problem but it is rather complicated to do so.

• The first task is to compare values for each cell in two columns in Sheet2 with another values in Sheet1. (e.g. if sh2.A1=sh1.B:B and sh2.C1=sh1.F:F then...)
• If those match, in the row where the match in Sheet1 was found, I need to find a value corresponding to a specific word (e.g. match on row 6 -> I am looking for P21 and P26 -> P21 was found this time in column R, P26 in column AA -> however, the values corresponding to those words are in column next to it (column S for P21 and column AB for P26)).
• Then, the final equation in the last column of Sheet2 is =P26+columnK*P21/1000.
• And the final number.

The fist draft of the equoation was:

if sh2.J=sh.1B and sh2.C=sh.1G then (find "P26")+K*(find "P21")/1000

To realise it, I was able to find corresponding numbers by using:

=IF(OR(COUNTIF(sheet1!B:B,'sheet2'!J5),(COUNTIF(sheet1!G:G,'sheet2'!C5))),"yes","no match")

while my idea was to replace "yes" by a formula which would be looking for P21 and P26.
Then I realized:

1. I need to know on what row the match happen to not to count with any random P21 and P26
2. I need to take values which are in a different column than P21 and P26 (tj given column+1)
3. P21 and P26 are randomly spread to different columns, there is no order for them.

Another idea was to create it by function "concatenate" together with "vlookup" which might be feasible but it would take about 22 column of "extra computations" to get the final result.

Would anyone know about other option?

Example of the excel file:

Sheet1

Sheet2

 

[Eric W]

Your explanation is a bit confusing, and I'm not sure the ranges always match what you say, but I'll take a crack at it. With Sheet1 looking like this:

	B	G	N	O	P	Q	R	S	T	U	V	W	X	Y
XXX
XXX
A4.11.10	100-30	P21	P26
XXX
XXX
A4.11.10	100-20	P26	P21
XXX

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]

[TD="align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]9[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

and Sheet2 looking like this:

	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
matching row	P21	P26	Formula1	Formula2	Formula3
100-20	A4.11.10
100-30	A4.11.10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]31[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5800[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]126[/TD]
[TD="align: right"]126[/TD]
[TD="align: right"]126[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q2[/TH]
[TD="align: left"]=INDEX(Sheet1!$C$2:$AB$100,P2,MATCH("P21",INDEX(Sheet1!$B$2:$AA$100,P2,0),0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]R2[/TH]
[TD="align: left"]=INDEX(Sheet1!$C$2:$AB$100,P2,MATCH("P26",INDEX(Sheet1!$B$2:$AA$100,P2,0),0))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]S2[/TH]
[TD="align: left"]=Q2+R2*K2/1000[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]T2[/TH]
[TD="align: left"]=INDEX(Sheet1!$C$2:$AB$100,P2,MATCH("P21",INDEX(Sheet1!$B$2:$AA$100,P2,0),0))+INDEX(Sheet1!$C$2:$AB$100,P2,MATCH("P26",INDEX(Sheet1!$B$2:$AA$100,P2,0),0))*K2/1000[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]P2[/TH]
[TD="align: left"]{=MATCH(J2&"|"&C2,Sheet1!$B$2:$B$100&"|"&Sheet1!$G$2:$G$100,0)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]U2[/TH]
[TD="align: left"]{=SUM(IF(Sheet1!$B$2:$B$100=J2,IF(Sheet1!$G$2:$G$100=C2,IF(Sheet1!$O$2:$AA$100="P21",Sheet1!$P$2:$AB$100))))+SUM(IF(Sheet1!$B$2:$B$100=J2,IF(Sheet1!$G$2:$G$100=C2,IF(Sheet1!$O$2:$AA$100="P26",Sheet1!$P$2:$AB$100))))*K2/1000}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



If I understand correctly, you want to find the row on Sheet1 where J2 matches column B on Sheet1, and C2 matches column G on Sheet1. Once you've found that row, find P21 on it and return the value to its right. Also find P26 and return the value on its right, then combine those values like this: v.21 + v.26*K2/1000.

P21 and P26 can appear in various columns. Am I right?

If so, let's start with the formula in P2. It's an array formula, and it just finds the matching row for J2 and C2. Now the formula in Q2 finds the P21 value. You could include the formula from P2 in the Q2 formula, but it would make it longer and less clear. The R2 formula is exactly the same as Q2, except for the P26 value, and in fact, it could read that value from row 1 making those formulas exactly the same. Finally the S2 formula combines the Q2 and R2 values as you wanted. If you prefer, you can combine Q2, R2, and S2 into one formula T2. If you really wanted, you could put the P2 formula into T2, giving you a single formula, but it would go in 4 times and make it quite unwieldy.

Finally, the U2 formula is a different approach. It requires no helper cells, but if your range on Sheet1 is large, it will be pretty inefficient.

Let me know if I'm on the right track.

 

[DanteAmor]

Put the following big formula in cell P2 in sheet2

[TABLE="class: grid, width: 900"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD]=IFERROR(INDEX(sheet1!$A$1:$AY$50,SUMPRODUCT((sheet1!$B$2:$B$50=J2)*(sheet1!$G$2:$G$50=C2)*(ROW(sheet1!$N$2:$N$50))), MATCH("P26",INDIRECT("sheet1!" & SUMPRODUCT((sheet1!$B$2:$B$50=J2)*(sheet1!$G$2:$G$50=C2)*(ROW(sheet1!$N$2:$N$50))) & ":" & SUMPRODUCT((sheet1!$B$2:$B$50=J2)*(sheet1!$G$2:$G$50=C2)*(ROW(sheet1!$N$2:$N$50)))),0) + 1) + K2 * INDEX(sheet1!$A$1:$AY$50,SUMPRODUCT((sheet1!$B$2:$B$50=J2)*(sheet1!$G$2:$G$50=C2)*(ROW(sheet1!$N$2:$N$50))), MATCH("P21",INDEX("sheet1!" & SUMPRODUCT((sheet1!$B$2:$B$50=J2)*(sheet1!$G$2:$G$50=C2)*(ROW(sheet1!$N$2:$N$50))) & ":" & SUMPRODUCT((sheet1!$B$2:$B$50=J2)*(sheet1!$G$2:$G$50=C2)*(ROW(sheet1!$N$2:$N$50)))),0) + 1) / 1000, "No match")[/TD]
[/TR]
</tbody>[/TABLE]


Change in the formula 50 by the number of the last row with data.

The formula is very long but it is because this part is used several times to find the row with the coincidence of cells C2 and J2

[TABLE="class: grid, width: 900"]
<tbody>[TR]
[TD]SUMPRODUCT((sheet1!$B$2:$B$50=J2)*(sheet1!$G$2:$G$50=C2)*(ROW(sheet1!$N$2:$N$50)))[/TD]
[/TR]
</tbody>[/TABLE]



If you can put that part in another column, for example, in Q2, the formula is substantially reduced and would look like this:

[TABLE="class: grid, width: 900"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]P2[/TD]
[TD]=IFERROR(INDEX(sheet1!$A$1:$AY$50,Q2, MATCH("P26",INDIRECT("sheet1!" & Q2 & ":" & Q2),0) + 1) + K2 * INDEX(sheet1!$A$1:$AY$50,Q2, MATCH("P21",INDIRECT("sheet1!" & Q2 & ":" & Q2),0) + 1) / 1000, "No match")[/TD]
[/TR]
[TR]
[TD]Q2[/TD]
[TD]=SUMPRODUCT((sheet1!$B$2:$B$50=J2)*(sheet1!$G$2:$G$50=C2)*(ROW(sheet1!$N$2:$N$50)))[/TD]
[/TR]
</tbody>[/TABLE]


Regards Dante Amor

 

[mtjanousek]

Hi all, thanks for all the support.

I tried both solutions but in the end, after adjusting to the real file, I went with the equations from DanteAmor since it seems to me so far that they are giving the best results.

However, I have noticed that there is a problem with my data. The values in sheet2 column C and J should be also connected to column B since the codes in C are not unique IDs - as I thought - but they are sometimes the same for different locations (locations are described by numbers in column B of sheet2 and column A sheet1).

Hence, it means to compare column B in Sheet2 with column A in Sheet1. I did it by editing the formula:

=SUMPRODUCT((sheet1!$B$2:$B$169027=J2)*(sheet1!$G$2:$G$169027=C2)*[B][COLOR=#800080](sheet1!$A$2:$G$169027=B2)[/COLOR][/B]*(ROW(sheet1!$N$2:$N$169027)))

However, since the computer has to compare 3 columns with another 3 while sheet1 has 169027 rows and sheet2 115154 rows, the computer cannot handle it...
Would you know if there is a possibility to simplify it? Other solution would be to divide it into more files according to locations, but that would lead to some other issues.

Thanks a lot once again.