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 fist draft of the equoation was:
To realise it, I was able to find corresponding numbers by using:
while my idea was to replace "yes" by a formula which would be looking for P21 and P26.
Then I realized:
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
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:
- I need to know on what row the match happen to not to count with any random P21 and P26
- I need to take values which are in a different column than P21 and P26 (tj given column+1)
- 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