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
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

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

1​

[/td][td]

Name​

[/td][td]

Type​

[/td][td]

Quantity​

[/td][td]

​

[/td][td]

Sum of Quantity​

[/td][td]

Column Labels​

[/td][td]

​

[/td][/tr]


[tr][td]

2​

[/td][td]

John​

[/td][td]

In​

[/td][td]

6​

[/td][td]

​

[/td][td]

Row Labels​

[/td][td]

In​

[/td][td]

Out​

[/td][/tr]


[tr][td]

3​

[/td][td]

Simon​

[/td][td]

In​

[/td][td]

6​

[/td][td]

​

[/td][td]

John​

[/td][td]

7​

[/td][td]

12​

[/td][/tr]


[tr][td]

4​

[/td][td]

Simon​

[/td][td]

In​

[/td][td]

6​

[/td][td]

​

[/td][td]

Matt​

[/td][td]

1​

[/td][td]

7​

[/td][/tr]


[tr][td]

5​

[/td][td]

Simon​

[/td][td]

Out​

[/td][td]

7​

[/td][td]

​

[/td][td]

Simon​

[/td][td]

12​

[/td][td]

10​

[/td][/tr]


[tr][td]

6​

[/td][td]

Matt​

[/td][td]

Out​

[/td][td]

7​

[/td][td]

​

[/td][td]

Grand Total​

[/td][td]

20​

[/td][td]

29​

[/td][/tr]


[tr][td]

7​

[/td][td]

John​

[/td][td]

In​

[/td][td]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

8​

[/td][td]

John​

[/td][td]

Out​

[/td][td]

12​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

9​

[/td][td]

Simon​

[/td][td]

Out​

[/td][td]

3​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]


[tr][td]

10​

[/td][td]

Matt​

[/td][td]

In​

[/td][td]

1​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

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

 

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