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

# Min number from 2 columns

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

 Name No. of days N1 59.00 N1 138.00 N1 207.00 N1 266.00 N2 38.00 N2 100.00 N2 153.00 N2 211.00 N2 229.00 N2 247.00 N2 285.00 N2 292.00 N3 185.00 N4 574.00 N5 31.00 N5 217.00 N5 284.00

Thanks

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

# Re: Min number from 2 columns

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:

# Re: Min number from 2 columns

Thank you very much. Worked like a charm.

Nikhil

# Re: Min number from 2 columns

1 best response

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

# Re: Min number from 2 columns

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: