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
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

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>
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,228
Messages
5,509,922
Members
408,764
Latest member
Abdul Matin

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top