list to crosstab with help of function--No macros

roshankp

New Member
Joined
Aug 10, 2010
Messages
16
Col-A Col-B Unit Price
a 10 100
a 20 200
a 30 300
b 10 450
b 20 500
b 30 550








Output


Col 10 20 30

a 100 200 300

b 450 500 550
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
You have to some manual work if u r trying to do this without macros.

Better try paste special.. then select transpose.

Ranjith
 
Upvote 0
Try

I1: =B1
J1: =IF(ISERROR(SMALL(IF(NOT(ISNUMBER(MATCH($B$1:$B$6,$I$1:I$1,0))),ROW($B$1:$B$6)),COLUMN(A1))),"",
INDEX($B$1:$B$6,SMALL(IF(NOT(ISNUMBER(MATCH($B$1:$B$6,$I$1:I$1,0))),ROW($B$1:$B$6)),COLUMN(A1))))

array-entered, and copy across as far as is needed

H2: =A1
H3: =IF(ISERROR(SMALL(IF(NOT(ISNUMBER(MATCH($A$1:$A$6,$H$2:$H2,0))),ROW($A$1:$A$6)),ROW(A1))),"",
INDEX($A$1:$A$6,SMALL(IF(NOT(ISNUMBER(MATCH(A$1:$A$6,$H$2:$H2,0))),ROW($A$1:$A$6)),ROW(A1))))

array entered and copy down as far as is needed

I2: =IF(AND(I$1<>"",$H2<>""),SUMPRODUCT(--($H2=$A$1:$A$6),--(I$1=$B$1:$B$6),$C$1:$C$6),"")

copied down and across in line with others
 
Upvote 0
Hi Roshankp

Assume your source data is a2:c6. If I understand you correctly, a, b,.etc will be your row headings. 10,20,30, etc will be your column headings. First, do the row headings. Go to e2 enter=index($a$2:$A$6,small(if(frequency(if($a2:$a$6<>"",match($a$2:$a$6,$a2$a6,0)),row($a$2:$a$6)-row($a2)+1),row($a$2:$a$6)-row($a$2)+1),rows($e$2:e2))). Use C-S-E, copy down. This will produce a,b, and other letters if in range.

Let's work on the column headers which exclude duplicate numbers. Go to F1 enter:=index($b$2:$b$6,small(if(frequency(if($b$2:$b$6,$b2$b$6)>0,row)$b$2:$b$6)-row($b$2)+1),columns($f$1:f1))). Use C-S-E.,copy accross. This will give 10, 20,30, any other numbers to be in the range.


Now the data in the range. Go to F2. Enter: =index($c$2:$c$6,small(if($a$2:$a$6=$e2,row($c$2:$c$6)-row($c$2)+1),columns($f$2:f2))). Copy down and accross. Use $e2, as you want to copy formula down so formula can use criteria b.


HTH,
Mike Szczesny
 
Upvote 0
But of course, insert a header row and pivot it is the simplest of all
 
Upvote 0
I finally figured out how to use Jeanie. I hope this may be helpful

Mike Szczesny


Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>a</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">100</TD><TD> </TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">30</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>a</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">200</TD><TD>a</TD><TD style="TEXT-ALIGN: right">100</TD><TD style="TEXT-ALIGN: right">200</TD><TD style="TEXT-ALIGN: right">300</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>a</TD><TD style="TEXT-ALIGN: right">30</TD><TD style="TEXT-ALIGN: right">300</TD><TD>b</TD><TD style="TEXT-ALIGN: right">450</TD><TD style="TEXT-ALIGN: right">500</TD><TD style="TEXT-ALIGN: right">550</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>b</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">450</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>b</TD><TD style="TEXT-ALIGN: right">20</TD><TD style="TEXT-ALIGN: right">500</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD>b</TD><TD style="TEXT-ALIGN: right">30</TD><TD style="TEXT-ALIGN: right">550</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>G5</TD><TD>{=INDEX($D$5:$D$10,SMALL(IF(FREQUENCY($D$5:$D$10,$D$5:$D$10)>0,ROW($D$5:$D$10)-ROW($D$5)+1),COLUMNS($G$5:G5)))}</TD></TR><TR><TD>H5</TD><TD>{=INDEX($D$5:$D$10,SMALL(IF(FREQUENCY($D$5:$D$10,$D$5:$D$10)>0,ROW($D$5:$D$10)-ROW($D$5)+1),COLUMNS($G$5:H5)))}</TD></TR><TR><TD>I5</TD><TD>{=INDEX($D$5:$D$10,SMALL(IF(FREQUENCY($D$5:$D$10,$D$5:$D$10)>0,ROW($D$5:$D$10)-ROW($D$5)+1),COLUMNS($G$5:I5)))}</TD></TR><TR><TD>F6</TD><TD>{=INDEX($C$5:$C$10,SMALL(IF(FREQUENCY(IF($C$5:$C$10<>"",MATCH($C$5:$C$10,$C$5:$C$10,0)),ROW($C$5:$C$10)-ROW($C$5)+1),ROW($C$5:$C$10)-ROW($C$5)+1),ROWS($F$6:F6)))}</TD></TR><TR><TD>G6</TD><TD>{=INDEX($E$5:$E$10,SMALL(IF($C$5:$C$10=$F6,ROW($E$5:$E$10)-ROW($E$5)+1),COLUMNS($G$6:G6)))}</TD></TR><TR><TD>H6</TD><TD>{=INDEX($E$5:$E$10,SMALL(IF($C$5:$C$10=$F6,ROW($E$5:$E$10)-ROW($E$5)+1),COLUMNS($G$6:H6)))}</TD></TR><TR><TD>I6</TD><TD>{=INDEX($E$5:$E$10,SMALL(IF($C$5:$C$10=$F6,ROW($E$5:$E$10)-ROW($E$5)+1),COLUMNS($G$6:I6)))}</TD></TR><TR><TD>F7</TD><TD>{=INDEX($C$5:$C$10,SMALL(IF(FREQUENCY(IF($C$5:$C$10<>"",MATCH($C$5:$C$10,$C$5:$C$10,0)),ROW($C$5:$C$10)-ROW($C$5)+1),ROW($C$5:$C$10)-ROW($C$5)+1),ROWS($F$6:F7)))}</TD></TR><TR><TD>G7</TD><TD>{=INDEX($E$5:$E$10,SMALL(IF($C$5:$C$10=$F7,ROW($E$5:$E$10)-ROW($E$5)+1),COLUMNS($G$6:G7)))}</TD></TR><TR><TD>H7</TD><TD>{=INDEX($E$5:$E$10,SMALL(IF($C$5:$C$10=$F7,ROW($E$5:$E$10)-ROW($E$5)+1),COLUMNS($G$6:H7)))}</TD></TR><TR><TD>I7</TD><TD>{=INDEX($E$5:$E$10,SMALL(IF($C$5:$C$10=$F7,ROW($E$5:$E$10)-ROW($E$5)+1),COLUMNS($G$6:I7)))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top