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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

domex

Board Regular
Joined
Feb 25, 2010
Messages
144
You have to some manual work if u r trying to do this without macros.

Better try paste special.. then select transpose.

Ranjith
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
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
 

Mike Szczesny

Active Member
Joined
Feb 7, 2008
Messages
411
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
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
But of course, insert a header row and pivot it is the simplest of all
 

Mike Szczesny

Active Member
Joined
Feb 7, 2008
Messages
411
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>
 

Watch MrExcel Video

Forum statistics

Threads
1,133,755
Messages
5,660,759
Members
418,592
Latest member
Mel0620

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
Top