extracting data from table

neilp

Well-known Member
Joined
Jul 5, 2004
Messages
505
Hi

I have a long series of data that contains names in column A, Products in column B and quantities in column C
eg:
<table>
<tr>
<th>A</th>
<th>B</th>
<th>C</th>
</tr>
<tr>
<td>John</td>
<td>Apples</td>
<td>7</td>
</tr>
<tr>
<td>Steve</td>
<td>Apples</td>
<td>4</td>
</tr>
<tr>
<td>Pete</td>
<td>Bananas</td>
<td>2</td>
</tr>
<tr>
<td>John</td>
<td>Melon</td>
<td>1</td>
</tr>
<tr>
<td>Pete</td>
<td>Apples</td>
<td>6</td>
</tr>
<tr>
<td>Dave</td>
<td>Bananas</td>
<td>3</td>
</tr>
</table>



I need to be able to move the quantites into a pre built table that has a list of the names down in 1 column and a list of the products across the top row.
eg
<table>
<tr>
<th>Name</th>
<th>Apples</th>
<th>Bananas</th>
<th>Melon</th>
</tr>
<tr>
<td>John</td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>Steve</td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>Pete</td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>Dave</td>
<td></td>
<td></td>
<td></td>
</tr>
</table>


Anyone able to help?

thanks

Neil
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,717
using PowerQuery (Get&Transform)

Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table31"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[Fruit]), "Fruit", "Qty", List.Sum)
in
    #"Pivoted Column"[/SIZE]

NameFruitQtyNameApplesBananasMelon
JohnApples
7​
Dave
3​
SteveApples
4​
John
7​
1​
PeteBananas
2​
Pete
6​
2​
JohnMelon
1​
Steve
4​
PeteApples
6​
DaveBananas
3​
 

neilp

Well-known Member
Joined
Jul 5, 2004
Messages
505
Hi Sandy

Thanks for the reply, but you lost me at "PowerQuery".

How do i use that code?

thanks

Neil
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,430
Office Version
  1. 365
Platform
  1. Windows
Hi, here is a formula option you could try.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="color: #333333;;">John</td><td style="color: #333333;;">Apples</td><td style="text-align: right;color: #333333;;">7</td><td style=";"></td><td style="color: #333333;;">Name</td><td style="color: #333333;;">Apples</td><td style="color: #333333;;">Bananas</td><td style="color: #333333;;">Melon</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="color: #333333;;">Steve</td><td style="color: #333333;;">Apples</td><td style="text-align: right;color: #333333;;">4</td><td style=";"></td><td style="color: #333333;;">John</td><td style="text-align: right;color: #333333;background-color: #FFFF00;;">7</td><td style="text-align: right;color: #333333;;">0</td><td style="text-align: right;color: #333333;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="color: #333333;;">Pete</td><td style="color: #333333;;">Bananas</td><td style="text-align: right;color: #333333;;">2</td><td style=";"></td><td style="color: #333333;;">Steve</td><td style="text-align: right;color: #333333;;">4</td><td style="text-align: right;color: #333333;;">0</td><td style="text-align: right;color: #333333;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="color: #333333;;">John</td><td style="color: #333333;;">Melon</td><td style="text-align: right;color: #333333;;">1</td><td style=";"></td><td style="color: #333333;;">Pete</td><td style="text-align: right;color: #333333;;">6</td><td style="text-align: right;color: #333333;;">2</td><td style="text-align: right;color: #333333;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="color: #333333;;">Pete</td><td style="color: #333333;;">Apples</td><td style="text-align: right;color: #333333;;">6</td><td style=";"></td><td style="color: #333333;;">Dave</td><td style="text-align: right;color: #333333;;">0</td><td style="text-align: right;color: #333333;;">3</td><td style="text-align: right;color: #333333;;">0</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="color: #333333;;">Dave</td><td style="color: #333333;;">Bananas</td><td style="text-align: right;color: #333333;;">3</td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td><td style=";"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=SUMIFS(<font color="Blue">$C:$C,$A:$A,$E2,$B:$B,F$1</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,108,808
Messages
5,524,995
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top