• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help needed in making a list out of unique items

Nitin Panjwani

New Member
Hi team,

Need your help in making a list out of unique items.
In the attached excel, I have a list mentioned in columns B and C
I need the output as per columns F and G.

Kindly go through the attached excel file as I might not be able to interpret in detail here.
I am not sure if formulas or macro would be needed. Hence appreciate your help.
 

Attachments

  • help.xlsx
    10 KB · Views: 4
So you want a Cartesian Product/Join of lists.

Have a look at these links. It's common enough request.
Pivot table method - Excel 2013 and up
https://chandoo.org/wp/generate-all-combinations-from-two-lists-excel/

PQ method - Excel 2010/13 with PQ add-in, Excel 2016 and up
https://chandoo.org/forum/threads/l...culations-throughout-table.30536/#post-182191

MS Query method - Excel 2003 and up
https://chandoo.org/forum/threads/a...-to-another-list-of-values.29552/#post-176683

Formula method
https://www.mrexcel.com/forum/excel...ble-combinations-two-lists-without-macro.html
 
With modern dynamic arrays, simple calculations using array indices will return the pairs you need. First, as an aid to brevity and clarity, I define dimensions n and m for Place1 and Place2
= ROWS(Place1)
= ROWS(Place2)

I use this to generate an index k for the output list
= SEQUENCE( n*m )
The Cartesian products are then given by the pairs
= INDEX( Place1, 1 + MOD( k-1, n ) )
= INDEX( Place2, 1 + QUOTIENT( k-1, n ) )

There is a catch; SEQUENCE requires Office 365 monthly channel.
Alternatives are possible using INDIRECT and ROW.
 
Back
Top