question

bbb-0298 avatar image
0 Votes"
bbb-0298 asked VikiJi-MSFT commented

Excel Formula to find Unique values from Data Table

Thank you for taking the time to read my question.

I have a data table with many customers. Each customer has many sites. I'm wondering if it's possible to use Excel formulas to return the unique list of sites based on the Customer name entered into a cell. Next I'd like to be able to show the unique values horizontally.

Is this possible or do I need to write a quick macro?

Data Table:

Customer 1 Site 1 Other data
Customer 1 Site 1 Other data
Customer 1 Site 1 Other data
Customer 1 Site 1 Other data
Customer 1 Site 1 Other data
Customer 1 Site 2 Other data
Customer 1 Site 2 Other data
Customer 1 Site 2 Other data
Customer 1 Site 2 Other data
Customer 1 Site 3 Other data
Customer 2 Site A Other data
Customer 2 Site A Other data
Customer 2 Site B Other data
Customer 2 Site B Other data


User Input: Customer 1

Result I'd like to see

     A           B         C

1| Site 1 | Site 2 | Site 3

I found the Unique() function but I need to pass it a range of cells and I'm not sure how to do that. I also found the Transpose() function which I thought I could put the Unique() function in.

=Transpose(Unique("The range I'm not sure how to pass",True,True))

Thanks

office-excel-itpro
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @bbb-0298

With data in the grey table and your input in E1, is the result in E2-G2 what you expect?

117090-demo.png

If not please upload a similar picture showing the expected result

0 Votes 0 ·
demo.png (52.8 KiB)

1 Answer

VikiJi-MSFT avatar image
0 Votes"
VikiJi-MSFT answered VikiJi-MSFT commented

@bbb-0298,
Welcome to Q&A forum!

Please check whether the following formula is helpful to you, since it is an array formula, please press Ctrl+Shift+Enter to check after typing.
For more information about INDEX: INDEX function.

 =IFERROR(INDEX($B$2:$B$15,MATCH(0,IF($D2=$A$2:$A$15,COUNTIF($D2:D2,$B$2:$B$15),""),0)),"")

117344-image.png

Any updates, please let me know.


If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


image.png (49.7 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@VikiJi-MSFT

@bbb-0298 mentioned the UNIQUE function so I'm hoping s/he runs Excel 365 => would make things easy for her/him. Let's see...

An alternative to avoid Ctrl+Shift+Enter:

117455-demo.png

in E2:

 =IFERROR(
     INDEX($B$2:$B$15,
         AGGREGATE(15,6,ROW($A$2:$A$15) - ROW($A$1) /
             (($A$2:$A$15=$D2) * (COUNTIF($D2:D2,$B$2:$B$15)=0)),
             1
         )
     ),
     ""
 )

0 Votes 0 ·
demo.png (50.2 KiB)

@Lz-3068, Thanks for your new idea.

@bbb-0298, if you have a chance to view this thread, please check if our infromation is helpful.

0 Votes 0 ·