Too complex for me! (Filter only unique entries, vlookup and sum I think)

[-emma-]

Hi all,

I am really stuck with what I want to do. Columns A to C holds information about our customer base who buy or sell us stock.

I am looking to create the results shown in E1:G4 automatically, with scope for this to automatically expand if a new customer name is added in column A.

A few things. Ideally I need the information in Column E to be in the order they appear. (So the newest customer appears at the bottom).

The "Type" result in Column F will always be the same.

I have attached a screen snip, hoping this will help.

Thank you for your time.

Emma

<a href="https://imgbb.com/"><img src="https://image.ibb.co/mDC3XS/Untitled.jpg" alt="Untitled" border="0"></a>

 

[alansidman]

Maybe a pivot table?

Data Range

	A​	B​	C​	D​	E​	F​	G​
1​	Name​	Type​	Quantity​	​	Sum of Quantity​	Column Labels​	​
2​	John​	In​	6​	​	Row Labels​	In​	Out​
3​	Simon​	In​	6​	​	John​	7​	12​
4​	Simon​	In​	6​	​	Matt​	1​	7​
5​	Simon​	Out​	7​	​	Simon​	12​	10​
6​	Matt​	Out​	7​	​	Grand Total​	20​	29​
7​	John​	In​	1​	​	​	​	​
8​	John​	Out​	12​	​	​	​	​
9​	Simon​	Out​	3​	​	​	​	​
10​	Matt​	In​	1​	​	​	​	​

 

[Peter_SSs]

I am looking to create the results shown in E1:G4 automatically, with scope for this to automatically expand if a new customer name is added in column A.

This formula approach should do that.
Copy these formulas down as far as you might ever need after adjusting the $100 references to a row number that will be greater than you will ever use in columns A:C

Book1
	A	B	C	D	E	F	G
1	Name	Type	Quantity		Name	Type	Total
2	John	In	6		John	In	7
3	Simon	In	6		Simon	In	12
4	Simon	In	6		Matt	In	1
5	Simon	Out	7
6	Matt	Out	7
7	John	In	1
8	John	Out	12
9	Simon	Out	3
10	Matt	In	1
11
In Total

Cell Formulas
Range	Formula
E2:E7	E2	=IFERROR(INDEX($A$2:$A$100,MATCH(0,INDEX(COUNTIF($E$1:E1,$A$2:$A$100)+(A$2:A$100=""),0),0)),"")
F2:F7	F2	=IF(E2="","","In")
G2:G7	G2	=IF(E2="","",SUMIFS(C$2:C$100,A$2:A$100,E2,B$2:B$100,F2))