list to crosstab with help of function--No macros

roshankp

New Member
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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
You have to some manual work if u r trying to do this without macros.

Better try paste special.. then select transpose.

Ranjith

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

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

But of course, insert a header row and pivot it is the simplest of all

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>

Replies
4
Views
104
Replies
18
Views
137
Replies
2
Views
98
Replies
4
Views
65
Replies
13
Views
146

1,196,250
Messages
6,014,232
Members
441,808
Latest member
xplainer

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.

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

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