Compare + Find + Write Equation

mtjanousek

New Member
Joined
Jul 25, 2018
Messages
17
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:
Code:
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:

Code:
=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

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,017
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:

BGNOPQRSTUVWXY
1
2XXX
3XXX
4A4.11.10100-30P2110P2620
5XXX
6XXX
7A4.11.10100-20P2611P219
8XXX

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1



and Sheet2 looking like this:

CDEFGHIJKLMNOPQRSTU
1matching rowP21P26Formula1Formula2Formula3
2100-20A4.11.1020006911313131
3100-30A4.11.10580031020126126126

<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>
</tbody>
Sheet2

Worksheet Formulas
CellFormula
Q2=INDEX(Sheet1!$C$2:$AB$100,P2,MATCH("P21",INDEX(Sheet1!$B$2:$AA$100,P2,0),0))
R2=INDEX(Sheet1!$C$2:$AB$100,P2,MATCH("P26",INDEX(Sheet1!$B$2:$AA$100,P2,0),0))
S2=Q2+R2*K2/1000
T2=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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
P2{=MATCH(J2&"|"&C2,Sheet1!$B$2:$B$100&"|"&Sheet1!$G$2:$G$100,0)}
U2{=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}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>



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

Well-known Member
Joined
Dec 3, 2018
Messages
8,970
Office Version
2007
Platform
Windows
Put the following big formula in cell P2 in sheet2

CellFormula
P2=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")

<tbody>
</tbody>


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

SUMPRODUCT((sheet1!$B$2:$B$50=J2)*(sheet1!$G$2:$G$50=C2)*(ROW(sheet1!$N$2:$N$50)))

<tbody>
</tbody>



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

CellFormula
P2=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")
Q2=SUMPRODUCT((sheet1!$B$2:$B$50=J2)*(sheet1!$G$2:$G$50=C2)*(ROW(sheet1!$N$2:$N$50)))

<tbody>
</tbody>


Regards Dante Amor
 

mtjanousek

New Member
Joined
Jul 25, 2018
Messages
17
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:
Code:
=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.
 

Forum statistics

Threads
1,082,585
Messages
5,366,466
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top