Presenting dynamic paired cells

[kirksmim]

Hi all,

Firstly apologies for the vague title, I had no idea what to call it.

My problem:
I have two sets of multiple drop down menus (data validation lists) (let's say in column A and B), and I want to present the information which repeats the values in column A for every value in column B, with the value in column B accompanying it.

Below I've attached two example of how I would want the data to look. (Letters are used for clarity, they will actually be fairly long strings of text)

Example 1
Excel 2012

	A	B	C	D	E
a	x	a	x
b	y	a	y
c	z	a	z
d	b	x
e	b	y
b	z
c	x
c	y
c	z
d	x
d	y
d	z
e	x
e	y
e	z

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

Example 2
Excel 2012

	A	B	C	D	E
d	j	d	j
f	k	d	k
c	f	j
a	f	k
e	c	j
h	c	k
a	j
a	k
e	j
e	k
h	j
h	k

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

Remember:

• The cells in columns A and B will occupied by multiple drop-down menus (data validation lists), which will be deleted/added at will by the user. They won't be in any order and they will be able to enter as many/few values as they require.

If possible I don't want to resort to a vba, and I'm sure it can be done using ingenuity (but if not that's fine).

If anyone can solve this I would be eternally grateful.

Many thanks,
Michael

 

[Eric W]

I recently answered a very similar question. I believe you can use the same formulas found in post #3 of this thread:

http://www.mrexcel.com/forum/excel-...a-produce-all-permutations-2-sets-values.html