extracting data from table

[neilp]

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]

using PowerQuery (Get&Transform)

[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]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Name[/td][td=bgcolor:#5B9BD5]Fruit[/td][td=bgcolor:#5B9BD5]Qty[/td][td][/td][td=bgcolor:#70AD47]Name[/td][td=bgcolor:#70AD47]Apples[/td][td=bgcolor:#70AD47]Bananas[/td][td=bgcolor:#70AD47]Melon[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]John[/td][td=bgcolor:#DDEBF7]Apples[/td][td=bgcolor:#DDEBF7]

7​

[/td][td][/td][td=bgcolor:#E2EFDA]Dave[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Steve[/td][td]Apples[/td][td]

4​

[/td][td][/td][td]John[/td][td]

7​

[/td][td][/td][td]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Pete[/td][td=bgcolor:#DDEBF7]Bananas[/td][td=bgcolor:#DDEBF7]

2​

[/td][td][/td][td=bgcolor:#E2EFDA]Pete[/td][td=bgcolor:#E2EFDA]

6​

[/td][td=bgcolor:#E2EFDA]

2​

[/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]John[/td][td]Melon[/td][td]

1​

[/td][td][/td][td]Steve[/td][td]

4​

[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Pete[/td][td=bgcolor:#DDEBF7]Apples[/td][td=bgcolor:#DDEBF7]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Dave[/td][td]Bananas[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[neilp]

Hi Sandy

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

How do i use that code?

thanks

Neil

 

[FormR]

Hi, here is a formula option you could try.

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	John	Apples	7		Name	Apples	Bananas	Melon
2	Steve	Apples	4		John	7	0	1
3	Pete	Bananas	2		Steve	4	0	0
4	John	Melon	1		Pete	6	2	0
5	Pete	Apples	6		Dave	0	3	0
6	Dave	Bananas	3
Sheet1

Cell Formulas
Range	Formula
F2	=SUMIFS($C:$C,$A:$A,$E2,$B:$B,F$1)

 

[sandy666]

Thanks for the reply, but you lost me at "PowerQuery".
How do i use that code?

1. what is your excel version, less than 2010 or higher?
2. what is PowerQuery