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



Comments

Post a Comment

Popular posts from this blog

Known solutions for hybrid migrations errors - Microsoft Community

-
technical level : intermediate summary this article documents of common errors related failed hybrid migrations, along steps resolve them or fix suggestions, there multiple root causes. in case need guidance extracting detailed migration errors or analyzing migration report powershell, please consult our below article, step step instructions: how extract , analyze errors hybrid migration report using powershell if had experienced new error missing list , found solution, more welcome share here, because consolidate initiative , other persons fix migration issues faster. details 1) migrationpermanentexception: must specify primaryonly parameter. error can occur when attempt migrate cloud on-prem mailbox via migration batch eac, online archive exists user. fix: in order migrate primary mailbox office 365, need start mailbox move following command, exchange online powershell: new-moverequest – identity <user > -remotec...
Read more

Microsoft Office 2013 reset rearm count - Microsoft Community

-
microsoft office 2013 rearm count has reached 0.  have tried uninstall microsoft office 2013 using add/remove programs, fixtool, , microsoft's vbs script.  have searched registry *0ff1ce , *office delete remaining registry keys.  unfortunately, when reinstall microsoft office 2013 on vmware windows 7 master image, rearm count not reset , continue receive error message "there error when trying rearm office.  can try passing sku id parameter.  passing sku id necessary if relying on activation to permit , additional rearm. error: 0xc004d307 on computer running microsoft windows non-core edition, run 'slui.exe 0x2a 0xc004d307 display the error text".   hi joe, there few ways have activation of office program rearmed through server or using command prompt commands. since you've performed few troubleshooting steps suggested on thread, best have raised on our technet foru...
Read more

Getting ErrorCode: 120018 when trying to access Microsoft account - Microsoft Community

-
split thread. hi, we 've got user exact same issue: "ce mot de passe été utilisé trop souvent dans le passé. choisissez une chaîne plus difficile à deviner. (which means: " we've seen password many times before. choose harder guess. ") errorcode: 120018 correlationid: 4ab3a486-a11a-4ead-983c-c1c391c3e7d5" it happens when tries update password "login.microsoftonline.com" portal. we tested more 5 different new passwords (passwords never seen before). office 365 synchronized ad; user's account not locked or whatsoever, account set able change password cannot reset it. thanks in advance. ***post moved moderator appropriate forum category.*** hi all,   thanks replies.   generally, password length greater 10 characters. understand users may want have longer passwords. please submit feedback improve our products , services. understandi...
Read more