extracting data from table

neilp

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

sandy666

Well-known Member
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

Active Member
Hi Sandy

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

How do i use that code?

thanks

Neil
 

FormR

MrExcel MVP
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 />
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top