Consolidate (or Remove Duplicates) on range with 4 columns, and count totals for 1 column only

[lamensterms]

Hey,

I'm not sure if this is a unique task or I'm just not using the right search terms when Googling... hoping to get steered in the right direction.

My situation... I regularly have to process poorly collated data (extracted from software that doesn't have very good reporting feature). The data is part lists for steel fabrication. The raw data is a formatted as a single row for each instance of a part, that is if I have 4-off of "part 1", there will be 4 rows, each with "part 1" showing a quantity of 1-off. I could use the Consolidate feature if I am working with only 2 columns, but I am working with 4 columns, and I only want to SUM 1 column's data. Sorry for the poor explanation

Does anyone know of a good method to achieve this? My current method works quite well, but is a bit tedious and I am hoping there is a more efficient way

What I've got:

What I need:

 

[lamensterms]

I should add... I've played around with Pivot Tables, but can't get it in the desired format.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[lamensterms]

Thanks Fluff.

Using Excel 365 16.0.12730

 

[Fluff]

Thanks for that
How about

=LET(u,UNIQUE(A2:D25),s,SUMIFS(B:B,A:A,INDEX(u,,1)),IF({1,2,3,4}=2,s,u))

 

[lamensterms]

Thanks for that
How about

=LET(u,UNIQUE(A2:D25),s,SUMIFS(B:B,A:A,INDEX(u,,1)),IF({1,2,3,4}=2,s,u))

Thanks Fluff.

Where can I apply this formula? I get a #NAME? error when pasting into cells adjacent to the source range

 

[Fluff]

Ok, sounds like you don't have the LET function, do you have the UNIQUE function?

 

[lamensterms]

Ok, sounds like you don't have the LET function, do you have the UNIQUE function?

That's right - no LET function. I do have UNIQUE

 

[Fluff]

Ok, how about

=IF({1,2,3,4}=2,SUMIFS(B:B,A:A,INDEX(UNIQUE(A2:D25),,1)),UNIQUE(A2:D25))

 

[lamensterms]

Ok, how about

=IF({1,2,3,4}=2,SUMIFS(B:B,A:A,INDEX(UNIQUE(A2:D25),,1)),UNIQUE(A2:D25))

Wow Fluff! That works great! Thanks a lot for that

How do you get a single cell formula to create a range like this?