Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change column to rows with a match
I need a worksheet formula that can do this :
data in columns: result: A B U V W X Y Z 1 bill 22 1 bill 22 7 4 7 22 2 bill 7 2 joe 5 5 3 bill 4 3 ian 8 10 8 6 4 bill 7 4 ann 3 8 10 5 bill 22 6 joe 5 7 joe 5 8 ian 8 9 ian 10 10 ian 8 11 ian 6 12 ann 3 13 ann 8 14 ann 10 and so on down to 1002 rows for column A Thanks every one bill gras |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change column to rows with a match
Here's a formulas model which delivers exactly the desired functionalities &
results Assume your source data in A2:B2 down to row 1001 (ie 1000 rows) (the data need not be sorted by col A, it can be in scrambled order) In T2: =IF(A2=0,"",IF(COUNTIF(A$2:A2,A2)1,"",ROW())) In U2: =IF(ROWS($1:1)COUNT(T:T),"",INDEX(A:A,SMALL(T:T,R OWS($1:1)))) CopyT2:U2 down. Col U will dynamically return the list of uniques from col A Put this in V2, array-enter ie press CTRL+SHIFT+ENTER to confirm the formula: =IF($U2="","",IF(COLUMNS($A:A)COUNTIF($A$2:$A$100 1,$U2),"",INDEX($B$2:$B$1001,SMALL(IF($A$2:$A$1001 =$U2,ROW($1:$1000)),COLUMNS($A:A))))) Copy V2 across to Z2 (say) -- you should copy across by as many cols required to cover the max expected number of corresponding figs per unique name -- then fill down. That should round it up and deliver exactly the final results that you seek in cols U to Z (hide away/minimize col T). If you need a serializer col, place this in S2, copied down: =IF(U2="","",ROWS($1:1)). You should modify the ranges in the expression in V2 to suit the actual extents (I catered for 1000 rows. Use the smallest range which is large enough). Success? Celebrate it, hit the YES below -- Max Singapore --- "bill gras" wrote: I need a worksheet formula that can do this : data in columns: result: A B U V W X Y Z 1 bill 22 1 bill 22 7 4 7 22 2 bill 7 2 joe 5 5 3 bill 4 3 ian 8 10 8 6 4 bill 7 4 ann 3 8 10 5 bill 22 6 joe 5 7 joe 5 8 ian 8 9 ian 10 10 ian 8 11 ian 6 12 ann 3 13 ann 8 14 ann 10 and so on down to 1002 rows for column A |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change column to rows with a match
Hi Max
I held little hope for what I needed , in one word "Amazing" !! Thank You MAX -- bill gras "Max" wrote: Here's a formulas model which delivers exactly the desired functionalities & results Assume your source data in A2:B2 down to row 1001 (ie 1000 rows) (the data need not be sorted by col A, it can be in scrambled order) In T2: =IF(A2=0,"",IF(COUNTIF(A$2:A2,A2)1,"",ROW())) In U2: =IF(ROWS($1:1)COUNT(T:T),"",INDEX(A:A,SMALL(T:T,R OWS($1:1)))) CopyT2:U2 down. Col U will dynamically return the list of uniques from col A Put this in V2, array-enter ie press CTRL+SHIFT+ENTER to confirm the formula: =IF($U2="","",IF(COLUMNS($A:A)COUNTIF($A$2:$A$100 1,$U2),"",INDEX($B$2:$B$1001,SMALL(IF($A$2:$A$1001 =$U2,ROW($1:$1000)),COLUMNS($A:A))))) Copy V2 across to Z2 (say) -- you should copy across by as many cols required to cover the max expected number of corresponding figs per unique name -- then fill down. That should round it up and deliver exactly the final results that you seek in cols U to Z (hide away/minimize col T). If you need a serializer col, place this in S2, copied down: =IF(U2="","",ROWS($1:1)). You should modify the ranges in the expression in V2 to suit the actual extents (I catered for 1000 rows. Use the smallest range which is large enough). Success? Celebrate it, hit the YES below -- Max Singapore --- "bill gras" wrote: I need a worksheet formula that can do this : data in columns: result: A B U V W X Y Z 1 bill 22 1 bill 22 7 4 7 22 2 bill 7 2 joe 5 5 3 bill 4 3 ian 8 10 8 6 4 bill 7 4 ann 3 8 10 5 bill 22 6 joe 5 7 joe 5 8 ian 8 9 ian 10 10 ian 8 11 ian 6 12 ann 3 13 ann 8 14 ann 10 and so on down to 1002 rows for column A |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
change column to rows with a match
Welcome, glad you liked it
-- Max Singapore ----- "bill gras" wrote in message ... Hi Max I held little hope for what I needed , in one word "Amazing" !! Thank You MAX |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change color of rows every time value in first column changes | Excel Worksheet Functions | |||
Group into rows by every change of Column A | Excel Discussion (Misc queries) | |||
Formula that will change subsequent cells in a column to match fir | Excel Discussion (Misc queries) | |||
Count rows that match criteria in 2 different column cell ranges | New Users to Excel | |||
Need rows in Column A removed if they fully or partially match with any Column B row | Excel Discussion (Misc queries) |