Return each unique combination from 2 columns and return other information from the same row

Question

I have a tool I am working on using the latest version of Excel (whatever it is) and I have a table which looks like this:

I want to take each unique combination of values from the Code and POBox fields from the top table, and make them show in the bottom table only once, with the time and volume columns totaling up in the bottom table for each of those unique combinations. The end result in the bottom table should look something like the following, given the table values above:

I hope I explained that well enough. Thanks in Advance! I have tried different combinations of the UNIQUE function, XLOOKUP, IF, IFS, CHOOSE, and I am stumped.

Where do you get volume from?

P.b
– P.b

2025-11-18 06:18:36 +00:00
Commented Nov 18 at 6:18 — P.b
– P.b, Commented Nov 18 at 6:18

R vE · Accepted Answer · 2025-11-18 08:38:42Z

2

Assuming the Volumes come from somewhere else in the data set, why not use a simple pivot table or the PIVOTBY function

answered Nov 18 at 8:38

R vE

805 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tom Sharpe Nov 18 at 9:25

.....or Groupby

PapaPinkMIst Nov 18 at 21:26

Volume is input by the user on another sheet. The P.O. Box field was just recently added so I am trying to figure out how to incorporate it into this tool. I will likely also need to incorporate the P.O. Box tracker column into the volume sheet as well

PapaPinkMIst Nov 18 at 21:37

Also, I do not have the PIVOTBY function

Ron Rosenfeld Nov 19 at 1:50

Then you don't have the latest version of Excel. What version do you have?

VBasic2008 · Accepted Answer · 2025-11-18 08:15:32Z

1

Sumup Uniques

Adjust the inputs in the first row.

=LET(data,A2:E11,ccol,1,pcol,2,scol,3,ecol,4,vcol,5,
    co,INDEX(data,,ccol),
    po,INDEX(data,,pcol),
    u,UNIQUE(HSTACK(co,po)),
    v,HSTACK((INDEX(data,,ecol)-INDEX(data,,scol))*24,INDEX(data,,vcol)),
    m,MAKEARRAY(ROWS(u),COLUMNS(v),LAMBDA(r,c,
        SUM(FILTER(INDEX(v,,c),(co=INDEX(u,r,1))*(po=INDEX(u,r,2)))))),
    HSTACK(u,m))

To get Volume before Time, switch the 'value' columns:

v,HSTACK(INDEX(data,,vcol),(INDEX(data,,ecol)-INDEX(data,,scol))*24),

edited Nov 18 at 8:15

answered Nov 18 at 8:01

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

Collectives™ on Stack Overflow

Return each unique combination from 2 columns and return other information from the same row

2 Answers 2

4 Comments

Sumup Uniques

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Sumup Uniques

Comments

Your Answer

Sign up or log in

Post as a guest

Related