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

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

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

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