How to combine data from various columns with different row number to - Microsoft Community

my data file numerous columns , number of rows having value in each column different

i want combine of these columns 1 column without blanks , duplicate values.

is there solution solve it? or needs vba?

please me!

thanks in advance!

a	b	c	d	e
data				result
9280	50100	45373		9280
90374	92995	94760		90374
11971	2175	72510		11971
17157	32596	83738		17157
2175	58745	79869		2175
19286	12044	26180		19286
27077	…	27757		27077
67871		40265		67871
55665		96302		55665
…		44788		50100
		…		92995
				32596
				58745
				12044
				45373
				94760
				72510
				83738
				79869
				26180
				27757
				40265
				96302
				44788

enter below formula in cell e2 & copy down:

=iferror(iferror(iferror(index($a$2:$a$100,match(0,index(countif(e$1:e1,$a$2:$a$100)+($a$2:$a$100=""),,),0)),index($b$2:$b$100,match(0,index(countif(e$1:e1,$b$2:$b$100)+($b$2:$b$100=""),,),0))),index($c$2:$c$100,match(0,index(countif(e$1:e1,$c$2:$c$100)+($c$2:$c$100=""),,),0))),"")

its normal (non-array) formula. presently extracts 3 columns. formula can expanded cover additional columns further nesting iferror, example below part covers column , column b & c have been included in above formula.:

index($a$2:$a$100,match(0,index(countif(e$1:e1,$a$2:$a$100)+($a$2:$a$100=""),,),0))

the above formula skip duplicates being mentioned multiple times , skips blanks.

regards,

amit tandon

www.globaliconnect.com

Office / Excel / Windows other / Office 2013