Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hlookup with multiple same values?
Microsoft 2003 - I have a row of data that contains user names. Below this
row is an "x" in each cell that meets a certain critieria that I already filled out. Now I want to combine all the names in the first row so there is only one name, not multiples of the same name, and insert the appropriate "X" below their name based on the entire spreadsheet I put together. Example below of how it looks now: Bob Frank Joe Bob Mary Frank Jon X X X X X X X X X X So I would like it to look like this: Bob Frank Joe Mary Jon X X X X X X X X X X Thanks! I had posted this earlier but the formula given did not work. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hlookup with multiple same values?
Can you get a list of unique names and plop that into a new sheet.
I put my list of 4 names in Sheet2 A1:D1 Then in sheet2!A1, I put this formula: =IF(SUMPRODUCT(--(Sheet1!$A$1:$G$1=$A$1),--(Sheet1!$A2:$G2="X"))0,"X","") And drag down as far as necessary (same number of rows as in Sheet1). Now comes the fun part. Drag the formula in A2 to B2 The formula in B2 will look like: =IF(SUMPRODUCT(--(Sheet1!$A$1:$G$1=$A$1),--(Sheet1!$A2:$G2="X"))0,"X","") (no change at all) Your job will be to change that $a$1 to $b$1 so it looks like: =IF(SUMPRODUCT(--(Sheet1!$A$1:$G$1=$B$1),--(Sheet1!$A2:$G2="X"))0,"X","") then drag down that column B. Then do this same thing for each of the remaining columns. duketter wrote: Microsoft 2003 - I have a row of data that contains user names. Below this row is an "x" in each cell that meets a certain critieria that I already filled out. Now I want to combine all the names in the first row so there is only one name, not multiples of the same name, and insert the appropriate "X" below their name based on the entire spreadsheet I put together. Example below of how it looks now: Bob Frank Joe Bob Mary Frank Jon X X X X X X X X X X So I would like it to look like this: Bob Frank Joe Mary Jon X X X X X X X X X X Thanks! I had posted this earlier but the formula given did not work. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hlookup with multiple same values?
This works great! Thanks! I have one question though, at the end you
mentioned the fun part is "Your job will be to change that $a$1 to $b$1 so it looks like:" Why not just put a$1 so then you don't have to change the letter each time the column changes? I did that and it seems to work, I was just checking to see if there is something I am not seeing/missing? Then you don't have to go and change each formula. "Dave Peterson" wrote: Can you get a list of unique names and plop that into a new sheet. I put my list of 4 names in Sheet2 A1:D1 Then in sheet2!A1, I put this formula: =IF(SUMPRODUCT(--(Sheet1!$A$1:$G$1=$A$1),--(Sheet1!$A2:$G2="X"))0,"X","") And drag down as far as necessary (same number of rows as in Sheet1). Now comes the fun part. Drag the formula in A2 to B2 The formula in B2 will look like: =IF(SUMPRODUCT(--(Sheet1!$A$1:$G$1=$A$1),--(Sheet1!$A2:$G2="X"))0,"X","") (no change at all) Your job will be to change that $a$1 to $b$1 so it looks like: =IF(SUMPRODUCT(--(Sheet1!$A$1:$G$1=$B$1),--(Sheet1!$A2:$G2="X"))0,"X","") then drag down that column B. Then do this same thing for each of the remaining columns. duketter wrote: Microsoft 2003 - I have a row of data that contains user names. Below this row is an "x" in each cell that meets a certain critieria that I already filled out. Now I want to combine all the names in the first row so there is only one name, not multiples of the same name, and insert the appropriate "X" below their name based on the entire spreadsheet I put together. Example below of how it looks now: Bob Frank Joe Bob Mary Frank Jon X X X X X X X X X X So I would like it to look like this: Bob Frank Joe Mary Jon X X X X X X X X X X Thanks! I had posted this earlier but the formula given did not work. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hlookup with multiple same values?
You're not missing something. I just screwed up.
Glad you saw the better solution. duketter wrote: This works great! Thanks! I have one question though, at the end you mentioned the fun part is "Your job will be to change that $a$1 to $b$1 so it looks like:" Why not just put a$1 so then you don't have to change the letter each time the column changes? I did that and it seems to work, I was just checking to see if there is something I am not seeing/missing? Then you don't have to go and change each formula. "Dave Peterson" wrote: Can you get a list of unique names and plop that into a new sheet. I put my list of 4 names in Sheet2 A1:D1 Then in sheet2!A1, I put this formula: =IF(SUMPRODUCT(--(Sheet1!$A$1:$G$1=$A$1),--(Sheet1!$A2:$G2="X"))0,"X","") And drag down as far as necessary (same number of rows as in Sheet1). Now comes the fun part. Drag the formula in A2 to B2 The formula in B2 will look like: =IF(SUMPRODUCT(--(Sheet1!$A$1:$G$1=$A$1),--(Sheet1!$A2:$G2="X"))0,"X","") (no change at all) Your job will be to change that $a$1 to $b$1 so it looks like: =IF(SUMPRODUCT(--(Sheet1!$A$1:$G$1=$B$1),--(Sheet1!$A2:$G2="X"))0,"X","") then drag down that column B. Then do this same thing for each of the remaining columns. duketter wrote: Microsoft 2003 - I have a row of data that contains user names. Below this row is an "x" in each cell that meets a certain critieria that I already filled out. Now I want to combine all the names in the first row so there is only one name, not multiples of the same name, and insert the appropriate "X" below their name based on the entire spreadsheet I put together. Example below of how it looks now: Bob Frank Joe Bob Mary Frank Jon X X X X X X X X X X So I would like it to look like this: Bob Frank Joe Mary Jon X X X X X X X X X X Thanks! I had posted this earlier but the formula given did not work. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HLookup returning values | Excel Worksheet Functions | |||
Summing HLookup Values | Excel Worksheet Functions | |||
How to see columns from which values were not returned by HLOOKUP functions? | Excel Worksheet Functions | |||
Multiple lookup values in =HLOOKUP | Excel Discussion (Misc queries) | |||
Hlookup to return a sum of values | Excel Worksheet Functions |