What is the best approach?

[Dallan1361]

I have a large dataset (c.75,000 rows) and I'm interested in the data from three columns.

1. Reference Number
2. Person
3. Value

I want a formula to show to me who spent what on each job and return information the below

Ref

Reference Number	Person	Value
1234	Joe Bloggs	10
1234	Blog Joes	35
9876	Joe Bloggs	14
9876	Dave Smith	12
9876	Leroy Jenkins	222
4232	Blog Joes	65
4232	Dave Smith	45

Apologies in advance if this is something quite straightforward and I'm overlooking it. I am aware that you can get this from a Pivot table, however, I want something that is unaffected by filtering and I can use as a new dataset.

The reason I can't sort by Reference Number to achieve the same result is that some 'Person' may appear multiple times on the same Reference Number, so I want something that will sum all of their Values associated with a respective Reference Number.

Ideally I would also like to be able to add further columns from the same dataset for further analysis but this is priority for the moment.

Hope you can help, thank you in advance!!

 

[RobP]

Hi,

welcome to the forum. I wasn;t sure if you were planning to search for a specific job name, so I tried this where the jobref is entered in B3 for example:

Book1
	A	B	C	D	E	F	G
1
2
3	Job Ref:	9876
4
5	Reference Number	Person	Value
6	9876	Joe Bloggs	10		Joe Bloggs	24
7	1234	Blog Joes	35		Dave Smith	12
8	9876	Joe Bloggs	14		Leroy Jenkins	222
9	9876	Dave Smith	12
10	9876	Leroy Jenkins	222
11	4232	Blog Joes	65
12	4232	Dave Smith	45
13
Sheet1

Cell Formulas
Range	Formula
E6:F8	E6	=LET(tots,SUMIFS(C6:C12,A6:A12,B3,B6:B12,UNIQUE(B6:B12)),newtots,FILTER(tots,tots>0,""),names,UNIQUE(FILTER(B6:B12,A6:A12=$B$3)),HSTACK(names,newtots))
Dynamic array formulas.

 

[Fluff]

Hi & welcome to MrExcel.
Maybe

=LET(u,UNIQUE(FILTER(A2:B10000,A2:A10000<>"")),HSTACK(u,SUMIFS(C:C,A:A,INDEX(u,,1),B:B,INDEX(u,,2))))

 

[Dallan1361]

Hi & welcome to MrExcel.
Maybe

=LET(u,UNIQUE(FILTER(A2:B10000,A2:A10000<>"")),HSTACK(u,SUMIFS(C:C,A:A,INDEX(u,,1),B:B,INDEX(u,,2))))

Thanks for the help! Worked perfectly

 

[Dallan1361]

Hi,

welcome to the forum. I wasn;t sure if you were planning to search for a specific job name, so I tried this where the jobref is entered in B3 for example:

Book1
	A	B	C	D	E	F	G
1
2
3	Job Ref:	9876
4
5	Reference Number	Person	Value
6	9876	Joe Bloggs	10		Joe Bloggs	24
7	1234	Blog Joes	35		Dave Smith	12
8	9876	Joe Bloggs	14		Leroy Jenkins	222
9	9876	Dave Smith	12
10	9876	Leroy Jenkins	222
11	4232	Blog Joes	65
12	4232	Dave Smith	45
13
Sheet1

Cell Formulas
Range	Formula
E6:F8	E6	=LET(tots,SUMIFS(C6:C12,A6:A12,B3,B6:B12,UNIQUE(B6:B12)),newtots,FILTER(tots,tots>0,""),names,UNIQUE(FILTER(B6:B12,A6:A12=$B$3)),HSTACK(names,newtots))
Dynamic array formulas.

Thanks for the assistance Rob!

 

[Fluff]

Glad we could help & thanks for the feedback.