Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Min number from 2 columns

Copper Contributor

Hi - I would like help with following

1. I have 2 columns data , Col A with names (one name could be appearing several times) and a number in column B in each row.

2. I want to know for each change in name in Col A, the minimum number in col B corresponding to that name 

 

NameNo. of days
N159.00
N1138.00
N1207.00
N1266.00
N238.00
N2100.00
N2153.00
N2211.00
N2229.00
N2247.00
N2285.00
N2292.00
N3185.00
N4574.00
N531.00
N5217.00
N5284.00

 

Thanks 

3 Replies
best response confirmed by Nikhil865 (Copper Contributor)
Solution

@Nikhil865 

Formula solution with tabled data:

=LET(
    UniqueNames, SORT(UNIQUE(Table1[Name])),
    minimum, MINIFS(Table1[No. of days], Table1[Name], UniqueNames),
    HSTACK(UniqueNames, minimum)
)

Pivot solution field arrangement:

Patrick2788_0-1697446016676.png

 

 

 

Thank you very much. Worked like a charm.

Nikhil
Glad to help. You're welcome!
1 best response

Accepted Solutions
best response confirmed by Nikhil865 (Copper Contributor)
Solution

@Nikhil865 

Formula solution with tabled data:

=LET(
    UniqueNames, SORT(UNIQUE(Table1[Name])),
    minimum, MINIFS(Table1[No. of days], Table1[Name], UniqueNames),
    HSTACK(UniqueNames, minimum)
)

Pivot solution field arrangement:

Patrick2788_0-1697446016676.png

 

 

 

View solution in original post