Text cell list sorting problem

[Harold123]

I have a large set of cells with Text inside I need to sort into lists and in such a way that each list produced from the first omits one cell. The difficulty for me in particular is that the number of cells can vary. To illustrate simply below is a simplified set of cells with one word in each. Each subsequently generated list has a different omission. In this sample there are 4 Cells in the list Column Giving rise to four columns of lists, had the list been 20 cells long there would have been 20 generated.

Orig
List 1 2 3 4
---------------------------------------------
Cat Dog Mouse Bird Cat
Dog Mouse Bird Cat Dog
Mouse Bird Cat Dog Mouse
Bird

From the list on the left 4 lists are generated all with one different cell missing. To complicate matters the list can be any number of cells typically 20 but can be as high as 40 and is very time consuming.
Can anyone see a formula here or is it VBA, any suggestions help really appreciate.

 

[Peter_SSs]

Here is a formula approach. Formula in B1 is copied across (as far as you like) and down (as far as you like).

Excel Workbook
	A	B	C	D	E	F
1	Cat	Dog	Cat	Cat	Cat
2	Dog	Mouse	Mouse	Dog	Dog
3	Mouse	Bird	Bird	Bird	Mouse
4	Bird
5
Lists

Here expanded to a list of 10

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Item 1	Item 2	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1
2	Item 2	Item 3	Item 3	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2
3	Item 3	Item 4	Item 4	Item 4	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3
4	Item 4	Item 5	Item 5	Item 5	Item 5	Item 4	Item 4	Item 4	Item 4	Item 4	Item 4
5	Item 5	Item 6	Item 6	Item 6	Item 6	Item 6	Item 5	Item 5	Item 5	Item 5	Item 5
6	Item 6	Item 7	Item 7	Item 7	Item 7	Item 7	Item 7	Item 6	Item 6	Item 6	Item 6
7	Item 7	Item 8	Item 8	Item 8	Item 8	Item 8	Item 8	Item 8	Item 7	Item 7	Item 7
8	Item 8	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9	Item 8	Item 8
9	Item 9	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 9
10	Item 10
11
Lists (2)

 

[Peter_SSs]

Marginally shorter.

Excel Workbook
	A	B	C	D	E	F
1	Cat	Dog	Cat	Cat	Cat
2	Dog	Mouse	Mouse	Dog	Dog
3	Mouse	Bird	Bird	Bird	Mouse
4	Bird
5
Lists (3)

 

[Peter_SSs]

.. & if you know to copy it across the number of columns equal to the number of data rows and down the number of rows one less than the number of data rows then a fair chunk can be removed as well.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Item 1	Item 2	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1
2	Item 2	Item 3	Item 3	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2
3	Item 3	Item 4	Item 4	Item 4	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3
4	Item 4	Item 5	Item 5	Item 5	Item 5	Item 4	Item 4	Item 4	Item 4	Item 4	Item 4
5	Item 5	Item 6	Item 6	Item 6	Item 6	Item 6	Item 5	Item 5	Item 5	Item 5	Item 5
6	Item 6	Item 7	Item 7	Item 7	Item 7	Item 7	Item 7	Item 6	Item 6	Item 6	Item 6
7	Item 7	Item 8	Item 8	Item 8	Item 8	Item 8	Item 8	Item 8	Item 7	Item 7	Item 7
8	Item 8	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9	Item 8	Item 8
9	Item 9	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 9
10	Item 10
Lists (4)

 

[Harold123]

Wow, well i knew the likely solution was beyond my knowledge of excel, i just didnt realise it was that far.
thank you so much that was so quick and worked perfectly. Thank you.

 

[Peter_SSs]

Wow, well i knew the likely solution was beyond my knowledge of excel, i just didnt realise it was that far.
thank you so much that was so quick and worked perfectly. Thank you.

You are very welcome. Thanks for the follow-up.

 

[Harold123]

Whoops I may have spoken to soon. On a short list your first formula worked perfectly.
I tried it on a list of 24 and it didnt work, So I altered it as Follows that didnt work either giving me duplicates.
Im assuming I have altered something I shouldnt have or havent altered enough.

=IF(OR(ROWS(B$1:B1)>=ROWS($A$1:$A$24),COLUMNS($B:B)>ROWS($A$1:$A$24)),"",INDEX($A$1:$A$24,AGGREGATE(15,6,ROW($A$1:$A$24)-ROW($A$1)+1,ROWS(B$1:B1)+(ROWS(B$1:B1)>=COLUMNS($B:B)))))

Its bound to be me sorry about that.

 

[Peter_SSs]

So I altered it as Follows that didnt work either giving me duplicates.

=IF(OR(ROWS(B$1:B1)>=ROWS($A$1:$A$24),COLUMNS($B:B)>ROWS($A$1:$A$24)),"",INDEX($A$1:$A$24,AGGREGATE(15,6,ROW($A$1:$A$24)-ROW($A$1)+1,ROWS(B$1:B1)+(ROWS(B$1:B1)>=COLUMNS($B:B)))))

.

That modification for 24 items is exactly right by my reckoning and works for me below (at least as far as I understand the objective).

Can you be more specific about your data and exactly what "duplicates" occurred & where. Perhaps show the sample data and output as I have been doing?

Excel 2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Item 1	Item 2	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1	Item 1
2	Item 2	Item 3	Item 3	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2	Item 2
3	Item 3	Item 4	Item 4	Item 4	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3	Item 3
4	Item 4	Item 5	Item 5	Item 5	Item 5	Item 4	Item 4	Item 4	Item 4	Item 4	Item 4	Item 4	Item 4	Item 4	Item 4	Item 4	Item 4	Item 4	Item 4	Item 4	Item 4	Item 4	Item 4	Item 4	Item 4
5	Item 5	Item 6	Item 6	Item 6	Item 6	Item 6	Item 5	Item 5	Item 5	Item 5	Item 5	Item 5	Item 5	Item 5	Item 5	Item 5	Item 5	Item 5	Item 5	Item 5	Item 5	Item 5	Item 5	Item 5	Item 5
6	Item 6	Item 7	Item 7	Item 7	Item 7	Item 7	Item 7	Item 6	Item 6	Item 6	Item 6	Item 6	Item 6	Item 6	Item 6	Item 6	Item 6	Item 6	Item 6	Item 6	Item 6	Item 6	Item 6	Item 6	Item 6
7	Item 7	Item 8	Item 8	Item 8	Item 8	Item 8	Item 8	Item 8	Item 7	Item 7	Item 7	Item 7	Item 7	Item 7	Item 7	Item 7	Item 7	Item 7	Item 7	Item 7	Item 7	Item 7	Item 7	Item 7	Item 7
8	Item 8	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9	Item 8	Item 8	Item 8	Item 8	Item 8	Item 8	Item 8	Item 8	Item 8	Item 8	Item 8	Item 8	Item 8	Item 8	Item 8	Item 8
9	Item 9	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9	Item 9
10	Item 10	Item 11	Item 11	Item 11	Item 11	Item 11	Item 11	Item 11	Item 11	Item 11	Item 11	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10	Item 10
11	Item 11	Item 12	Item 12	Item 12	Item 12	Item 12	Item 12	Item 12	Item 12	Item 12	Item 12	Item 12	Item 11	Item 11	Item 11	Item 11	Item 11	Item 11	Item 11	Item 11	Item 11	Item 11	Item 11	Item 11	Item 11
12	Item 12	Item 13	Item 13	Item 13	Item 13	Item 13	Item 13	Item 13	Item 13	Item 13	Item 13	Item 13	Item 13	Item 12	Item 12	Item 12	Item 12	Item 12	Item 12	Item 12	Item 12	Item 12	Item 12	Item 12	Item 12
13	Item 13	Item 14	Item 14	Item 14	Item 14	Item 14	Item 14	Item 14	Item 14	Item 14	Item 14	Item 14	Item 14	Item 14	Item 13	Item 13	Item 13	Item 13	Item 13	Item 13	Item 13	Item 13	Item 13	Item 13	Item 13
14	Item 14	Item 15	Item 15	Item 15	Item 15	Item 15	Item 15	Item 15	Item 15	Item 15	Item 15	Item 15	Item 15	Item 15	Item 15	Item 14	Item 14	Item 14	Item 14	Item 14	Item 14	Item 14	Item 14	Item 14	Item 14
15	Item 15	Item 16	Item 16	Item 16	Item 16	Item 16	Item 16	Item 16	Item 16	Item 16	Item 16	Item 16	Item 16	Item 16	Item 16	Item 16	Item 15	Item 15	Item 15	Item 15	Item 15	Item 15	Item 15	Item 15	Item 15
16	Item 16	Item 17	Item 17	Item 17	Item 17	Item 17	Item 17	Item 17	Item 17	Item 17	Item 17	Item 17	Item 17	Item 17	Item 17	Item 17	Item 17	Item 16	Item 16	Item 16	Item 16	Item 16	Item 16	Item 16	Item 16
17	Item 17	Item 18	Item 18	Item 18	Item 18	Item 18	Item 18	Item 18	Item 18	Item 18	Item 18	Item 18	Item 18	Item 18	Item 18	Item 18	Item 18	Item 18	Item 17	Item 17	Item 17	Item 17	Item 17	Item 17	Item 17
18	Item 18	Item 19	Item 19	Item 19	Item 19	Item 19	Item 19	Item 19	Item 19	Item 19	Item 19	Item 19	Item 19	Item 19	Item 19	Item 19	Item 19	Item 19	Item 19	Item 18	Item 18	Item 18	Item 18	Item 18	Item 18
19	Item 19	Item 20	Item 20	Item 20	Item 20	Item 20	Item 20	Item 20	Item 20	Item 20	Item 20	Item 20	Item 20	Item 20	Item 20	Item 20	Item 20	Item 20	Item 20	Item 20	Item 19	Item 19	Item 19	Item 19	Item 19
20	Item 20	Item 21	Item 21	Item 21	Item 21	Item 21	Item 21	Item 21	Item 21	Item 21	Item 21	Item 21	Item 21	Item 21	Item 21	Item 21	Item 21	Item 21	Item 21	Item 21	Item 21	Item 20	Item 20	Item 20	Item 20
21	Item 21	Item 22	Item 22	Item 22	Item 22	Item 22	Item 22	Item 22	Item 22	Item 22	Item 22	Item 22	Item 22	Item 22	Item 22	Item 22	Item 22	Item 22	Item 22	Item 22	Item 22	Item 22	Item 21	Item 21	Item 21
22	Item 22	Item 23	Item 23	Item 23	Item 23	Item 23	Item 23	Item 23	Item 23	Item 23	Item 23	Item 23	Item 23	Item 23	Item 23	Item 23	Item 23	Item 23	Item 23	Item 23	Item 23	Item 23	Item 23	Item 22	Item 22
23	Item 23	Item 24	Item 24	Item 24	Item 24	Item 24	Item 24	Item 24	Item 24	Item 24	Item 24	Item 24	Item 24	Item 24	Item 24	Item 24	Item 24	Item 24	Item 24	Item 24	Item 24	Item 24	Item 24	Item 24	Item 23
24	Item 24
Lists (5)

Cell Formulas
Range	Formula
B1	=IF(OR(ROWS(B$1:B1)>=ROWS($A$1:$A$24),COLUMNS($B:B)>ROWS($A$1:$A$24)),"",INDEX($A$1:$A$24,AGGREGATE(15,6,ROW($A$1:$A$24)-ROW($A$1)+1,ROWS(B$1:B1)+(ROWS(B$1:B1)>=COLUMNS($B:B)))))

 

[Harold123]

I am so sorry, and I did look before I replied but it was me, ( I had a mistake in my earlier formula that created the original list).
It does work perfectly I have even tesed it on larger lists now.
You have saved me such a lot of time and opened up new pastures for my Excel.
I am so sorry to have wasted yours like this.
Thnk you once again

 

[Peter_SSs]

I am so sorry, and I did look before I replied but it was me, ( I had a mistake in my earlier formula that created the original list).
It does work perfectly I have even tesed it on larger lists now.
You have saved me such a lot of time and opened up new pastures for my Excel.
I am so sorry to have wasted yours like this.
Thnk you once again

No problem. This is what the forum is for. Glad it worked out in the end.