Index Columns with formula - Help

[darrenmccoy]

Hi Experts, Can you please show me a formula to index all the ID's into one column? The columns are all different number of rows. Thanks

ContactID	ContactID	ContactID	ContactID	ContactID
10129844	10129173	9045269	9074122	9074288
10371234	11718250	9074122	9292922	10062097
10527196	12091877	9074288	9293480	10072521
11625021	20297702	9074933	9508873	11276250
11718507	25267457	9293480	9745594	14822020
11958065	25521911	9316463	9950002	20779918
13701329	30766558	9508873	10072521	29995999
13957162	33533523	9632347	10123336	31147578
16374399	35801942	9745594	10150678	35189996
20511756	41149059	9941415	10156050	39564871
22164399	60269693	9944854	10205385	61239083
22203836	61199984	9950002	10206921	80538609
23137200	61239083	10038992	10208381	87217481
28788338	61983304	10062097	10224986	88378052
28791091	70369885	10072521	10352220	91813644
28802324	74375079	10129173	10371621
28863649	74710469	10206921	10527196
	75495681	10307765	10973181
	76805333	10352100	11072581
	77046814	10371621	11183118
	77275493	10480335	11284862
		10973181	11536983
		11183118	11718250
		11276250	11718507
		11284862	12091877
		11536983
		11552432
		11718250
		11718710
		11774458
		11971550

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jasonb75]

As your Contract ID's all appear to be numeric, this should work (Office 365 only).

=UNIQUE(SMALL($A$2:$E$50,SEQUENCE(COUNT($A$2:$E$50))))

 

[darrenmccoy]

As your Contract ID's all appear to be numeric, this should work (Office 365 only).

=UNIQUE(SMALL($A$2:$E$50,SEQUENCE(COUNT($A$2:$E$50))))

HI, Thanks, I gave this a try but it didn't like it.
Came up with a #NAME? error
I tried it in GSheets too, and it just indexed one value.
This is the version Im using.

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B	C	D	E	F		H
1	ContactID	ContactID	ContactID	ContactID	ContactID
2	10129844	10129173	9045269	9074122	9074288			9045269
3	10371234	11718250	9074122	9292922	10062097			9074122
4	10527196	12091877	9074288	9293480	10072521			9074122
5	11625021	20297702	9074933	9508873	11276250			9074288
6	11718507	25267457	9293480	9745594	14822020			9074288
7	11958065	25521911	9316463	9950002	20779918			9074933
8	13701329	30766558	9508873	10072521	29995999			9292922
9	13957162	33533523	9632347	10123336	31147578			9293480
10	16374399	35801942	9745594	10150678	35189996			9293480
11	20511756	41149059	9941415	10156050	39564871			9316463
12	22164399	60269693	9944854	10205385	61239083			9508873
13	22203836	61199984	9950002	10206921	80538609			9508873
14	23137200	61239083	10038992	10208381	87217481			9632347
15	28788338	61983304	10062097	10224986	88378052			9745594
16	28791091	70369885	10072521	10352220	91813644			9745594
17	28802324	74375079	10129173	10371621				9941415
18	28863649	74710469	10206921	10527196				9944854
19		75495681	10307765	10973181				9950002
20		76805333	10352100	11072581				9950002
21		77046814	10371621	11183118				10038992
22		77275493	10480335	11284862				10062097
23			10973181	11536983				10062097
24			11183118	11718250				10072521
25			11276250	11718507				10072521
26			11284862	12091877				10072521
27			11536983					10123336
28			11552432					10129173
29			11718250					10129173
30			11718710					10129844
31			11774458					10150678
32			11971550					10156050
33								10205385
34								10206921
Main

Cell Formulas
Range	Formula
H2:H34	H2	=AGGREGATE(15,6,$A$2:$E$32/($A$2:$E$32<>""),ROWS(H$2:H2))

 

[darrenmccoy]

Thanks for that, how about (above)

Thank you, can you tell me what the (15,6 part of the formula means?

 

[darrenmccoy]

FYI for any future readers of this post. This formula works perfectly in GSheets.
=UNIQUE({A1:A;B1:B;C1:C;D1:D;E1:E}) in Cell F1
But I cant get it to work in Excel.

 

[jasonb75]

Thank you, can you tell me what the (15,6 part of the formula means?

Those are both function parameters, 15 is the function number for small, which is used to list your ID's in ascending order. If you change it to 14, the function number for large, then they would be in descending order. The 6 parameter tells the function to ignore any errors generated in the array, which would be caused here by the empty cells.

All of this information, and more, can be found in the built in help file that comes with Excel.

This formula works perfectly in GSheets.
=UNIQUE({A1:A;B1:B;C1:C;D1:D;E1:E}) in Cell F1
But I cant get it to work in Excel.

The syntax of google formulas is different to Excel, it might be possible to make similar work, but not identical.
Also, you're using Excel 2019, which doesn't have the UNIQUE function. That requires an up to date office 365 subscription. It will likely be included in the next non-subscription version of excel (assuming that one is released) but that remains to be seen.

 

[Fluff]

If you want unique vales only then you can use

=AGGREGATE(15,6,$A$2:$E$32/($A$2:$E$32<>"")/(ISNA(MATCH($A$2:$E$32,H$1:H1,0))),1)