Forum Discussion
Help needed with excel formula
| The data about the parts of machines in a plant is kept in the following manner (Table I) | ||||||||
| Table I | ||||||||
| Machine Id | Parts Included | |||||||
| M004 | P006 | P001 | ||||||
| M001 | P003 | P004 | P001 | P002 | ||||
| M003 | P007 | P005 | ||||||
| M002 | P007 | P002 | P005 | |||||
| The manager wants to present the data in the following format (Table II). The data in the range A14:H14 are headers | ||||||||
| Table II | ||||||||
| Parts Included | ||||||||
| Machine Id | P001 | P002 | P003 | P004 | P005 | P006 | P007 | |
| M001 | ||||||||
| M002 | ||||||||
| M003 | ||||||||
| M004 | ||||||||
| Write a formula that can be used to generate Table II from Table I. You should write your formula in the cell B15. The formula should be such that it can be copied from B15 to the entire range (B15:H18) | ||||||||
| You cannot change the data in Table I in any manner | ||||||||
In fact it's not very tricky.
COMPARE($A2;'Table 1'!$A$1:$A$4;0) search in column A in Table 1 for the position of the actual M-number in $A2 and returns the row number (lets call it r).
Then I use this row number r to search for the actual P-number (cell B$1) in row r in Table 1.
I search from column A to Z in row r in Table 1, but search from column B would have been ok as well as, as the P-numbers starts in column B and not A.
If the P-number do exist the COMPARE returns the column number - which I don't use - I just want to know that it exist. If the actual P-number don't exist in this row (r) the COMPARE will give an error and that's why I have inserted a IF.ERR around the hole statement.
And then finally I show a X if the P-number exist and an empty text if it doesn't.
Hope that make sense.
9 Replies
If you in sheet 1 called "Table 1" have this in A1:F4
M4 P1 P3 P4 P7 P9 M1 P2 P4 P1 M3 P9 P3 P5 M2 P8 P9 P4 P1 And in sheet 2 called "Table 2" have this in A1:J5
P1 P2 P3 P4 P5 P6 P7 P8 P9 M1 X X X M2 X X X X M3 X X X M4 X X X X X The formula in B2 (in Danish) is:
=HVIS(HVIS.FEJL(SAMMENLIGN(B$1;INDIREKTE("'Table 1'!A"&TEKST(SAMMENLIGN($A2;'Table 1'!$A$1:$A$4;0);0)):INDIREKTE("'Table 1'!Z"&TEKST(SAMMENLIGN($A2;'Table 1'!$A$1:$A$4;0);0));0);0)>0;"X";"")
I think that it should be something like this in an English version:
=IF(IF.ERR(COMPARE(B$1;INDIRECT("'Table 1'!A"&TEXT(COMPARE($A2;'Table 1'!$A$1:$A$4;0);0)):INDIRECT("'Table 1'!Z"&TEXT(COMPARE($A2;'Table 1'!$A$1:$A$4;0);0));0);0)>0;"X";"")
- Thank you for the solution. I appreciate your efforts. I didn't know that it would be that tricky. Now, I'll have to devote some time to understand your solution :)
In fact it's not very tricky.
COMPARE($A2;'Table 1'!$A$1:$A$4;0) search in column A in Table 1 for the position of the actual M-number in $A2 and returns the row number (lets call it r).
Then I use this row number r to search for the actual P-number (cell B$1) in row r in Table 1.
I search from column A to Z in row r in Table 1, but search from column B would have been ok as well as, as the P-numbers starts in column B and not A.
If the P-number do exist the COMPARE returns the column number - which I don't use - I just want to know that it exist. If the actual P-number don't exist in this row (r) the COMPARE will give an error and that's why I have inserted a IF.ERR around the hole statement.
And then finally I show a X if the P-number exist and an empty text if it doesn't.
Hope that make sense.
I wish you would have understood my comment and not solved the asker's homework for them.
This encourages people to cheat and ask for solutions instead of working on the solution themselves.
It would have been a much better learning experience for the asker if you had given them the chance to post their own approach and then teach based on that. You have given him a fish that he has not earned and he still does not know how to fish.
- My purpose is to understand how it can be done or solved instead of just getting the solution and not having any clue about what just happened.
Hello,
this sounds like a homework assignment and you are trying to make us do the work for you.
We can help if you are stuck, but you have to post what you have tried and explain what is not working for you.
Sorry, but we can't do your homework for you.
