• 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

Peter Bartholomew

Well-Known Member
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.
 
Top