|
Formula Question (Organizing Data)
Hello everyone, I have a spread sheet which contains multiple row entries for
users with multiple columns for various settings. It looks something like this: Name Switch1 Switch2 Switch3 Switch4 Switch5 Bill True False False False False Bill False True False False False Bill False False True False False Mary True False False False False Joe False True False False False Joe True False False False False Joe False False False False True There are hundreds of entries for these users and I'm trying to capture this on another sheet with each user listed once and all of the switch settings which are true to show "True" else "False" (or blank): Name Switch1 Switch2 Switch3 Switch4 Switch5 Bill True True True False False Mary True False False False False Joe True True False False True I'm guessing some kind of nested 'and' formula or a lookup but can't seem to make anything work. Any help here would be appreciated, thanks! -- If you can read this, thank a Teacher... If your reading it in english, thank a Veteran! |
Formula Question (Organizing Data)
Maybe something like this:
With your posted list in A1:F8 And I1:N1 containing the column headings from A1:F1 (Name, Switch1, etc) Then..... First, let's list the unique names with this ARRAY FORMULA* I2: =IF(SUM(($A$1:$A$10<"")*ISERROR(MATCH($A$1:$A$10, $I$1:I1,0)))<0,INDEX($A$1:$A$10,MATCH(1,--ISERROR(IF(ISBLANK($A$1:$A$10),0,MATCH($A$1:$A$10, $I$1:$I1,0))),0),1),"") Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Copy I2 and paste it into I3 and down as far as you need Next, we'll calculate the switch values with this ARRAY FORMULA J2: =MAX(($A$2:$A$10=$I2)*B$2:B$10)=1 (remember to use [ctrl]+[shift]+[enter] ) Copy J2 and paste into K2 and across through N2 Copy J2 through N2 and paste into J3 and down as far as you need Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Telobamipada" wrote: Hello everyone, I have a spread sheet which contains multiple row entries for users with multiple columns for various settings. It looks something like this: Name Switch1 Switch2 Switch3 Switch4 Switch5 Bill True False False False False Bill False True False False False Bill False False True False False Mary True False False False False Joe False True False False False Joe True False False False False Joe False False False False True There are hundreds of entries for these users and I'm trying to capture this on another sheet with each user listed once and all of the switch settings which are true to show "True" else "False" (or blank): Name Switch1 Switch2 Switch3 Switch4 Switch5 Bill True True True False False Mary True False False False False Joe True True False False True I'm guessing some kind of nested 'and' formula or a lookup but can't seem to make anything work. Any help here would be appreciated, thanks! -- If you can read this, thank a Teacher... If your reading it in english, thank a Veteran! |
Formula Question (Organizing Data)
Well, it's no wonder I couldn't solve this one! Just a tad out of my league,
but brilliant. I will store this one away for safe keeping, thank you! -- If you can read this, thank a Teacher... If your reading it in english, thank a Veteran! "Ron Coderre" wrote: Maybe something like this: With your posted list in A1:F8 And I1:N1 containing the column headings from A1:F1 (Name, Switch1, etc) Then..... First, let's list the unique names with this ARRAY FORMULA* I2: =IF(SUM(($A$1:$A$10<"")*ISERROR(MATCH($A$1:$A$10, $I$1:I1,0)))<0,INDEX($A$1:$A$10,MATCH(1,--ISERROR(IF(ISBLANK($A$1:$A$10),0,MATCH($A$1:$A$10, $I$1:$I1,0))),0),1),"") Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Copy I2 and paste it into I3 and down as far as you need Next, we'll calculate the switch values with this ARRAY FORMULA J2: =MAX(($A$2:$A$10=$I2)*B$2:B$10)=1 (remember to use [ctrl]+[shift]+[enter] ) Copy J2 and paste into K2 and across through N2 Copy J2 through N2 and paste into J3 and down as far as you need Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Telobamipada" wrote: Hello everyone, I have a spread sheet which contains multiple row entries for users with multiple columns for various settings. It looks something like this: Name Switch1 Switch2 Switch3 Switch4 Switch5 Bill True False False False False Bill False True False False False Bill False False True False False Mary True False False False False Joe False True False False False Joe True False False False False Joe False False False False True There are hundreds of entries for these users and I'm trying to capture this on another sheet with each user listed once and all of the switch settings which are true to show "True" else "False" (or blank): Name Switch1 Switch2 Switch3 Switch4 Switch5 Bill True True True False False Mary True False False False False Joe True True False False True I'm guessing some kind of nested 'and' formula or a lookup but can't seem to make anything work. Any help here would be appreciated, thanks! -- If you can read this, thank a Teacher... If your reading it in english, thank a Veteran! |
Formula Question (Organizing Data)
Thanks for the feedback.....I'm glad that worked for you.
*********** Regards, Ron XL2002, WinXP "Telobamipada" wrote: Well, it's no wonder I couldn't solve this one! Just a tad out of my league, but brilliant. I will store this one away for safe keeping, thank you! -- If you can read this, thank a Teacher... If your reading it in english, thank a Veteran! "Ron Coderre" wrote: Maybe something like this: With your posted list in A1:F8 And I1:N1 containing the column headings from A1:F1 (Name, Switch1, etc) Then..... First, let's list the unique names with this ARRAY FORMULA* I2: =IF(SUM(($A$1:$A$10<"")*ISERROR(MATCH($A$1:$A$10, $I$1:I1,0)))<0,INDEX($A$1:$A$10,MATCH(1,--ISERROR(IF(ISBLANK($A$1:$A$10),0,MATCH($A$1:$A$10, $I$1:$I1,0))),0),1),"") Note: For array formulas, hold down [Ctrl] and [Shift] when you press [Enter], instead of just pressing [Enter]. Copy I2 and paste it into I3 and down as far as you need Next, we'll calculate the switch values with this ARRAY FORMULA J2: =MAX(($A$2:$A$10=$I2)*B$2:B$10)=1 (remember to use [ctrl]+[shift]+[enter] ) Copy J2 and paste into K2 and across through N2 Copy J2 through N2 and paste into J3 and down as far as you need Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Telobamipada" wrote: Hello everyone, I have a spread sheet which contains multiple row entries for users with multiple columns for various settings. It looks something like this: Name Switch1 Switch2 Switch3 Switch4 Switch5 Bill True False False False False Bill False True False False False Bill False False True False False Mary True False False False False Joe False True False False False Joe True False False False False Joe False False False False True There are hundreds of entries for these users and I'm trying to capture this on another sheet with each user listed once and all of the switch settings which are true to show "True" else "False" (or blank): Name Switch1 Switch2 Switch3 Switch4 Switch5 Bill True True True False False Mary True False False False False Joe True True False False True I'm guessing some kind of nested 'and' formula or a lookup but can't seem to make anything work. Any help here would be appreciated, thanks! -- If you can read this, thank a Teacher... If your reading it in english, thank a Veteran! |
| All times are GMT +1. The time now is 05:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com