Simple Excel Formula Question, CONCATENATE columns 1

[Zugdud]

Hi folks, I am trying to merge 5 columns of data in an excel sheet into a single column of data. Each column contains a partial list of names some rows have multiple names and some rows have zero names. If there are multiple names in a row i would just like to ignore them and just take the frist name for the merge, which one doesn't matter, just needs to be one name and not 3 names concatinated. Hope that makes sense, heres a sample of data I am dealing with and desired results posted below:

Company Contact1 Contact2 Contact3 Contact4 Contact 5

ICSCorp Bob Kevin
BoxCo John
TVLand
Teletime Rich
Yukon John Billy jill


Desired Result:

Company Contact
ICSCorp Bob
BoxCo John
TVLand
Teletime Rich
Yukon John




=CONCATENATE(H1,I1,J1, K1, L1, M1) Will do what I want BUT if a row contains more then one name it will just put both names in the new column which I dont want to happen. Is there any way to do this? Thankyou in advance for any help

 

[fredericofonseca]

=if(h11<>"",h1,if(i1<>"",i1,if(j1<>"",j1,""),""),"")

The above will do three variables (h1, i1, j1), and will retrieve the first one that is not spaces.

Adapt for more.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[Zugdud]

Holysmokes, WOW, that was quick! And it worked awesome! Man this has saved me a ton of time.

For whatever reason I had to remove the end set of quotes and commas, not sure if they were suppose to be there or not. Excel said I had too many arguments So incase this is useful to anyone doing searches:

=IF(H1<>"",H1,IF(I1<>"",I1,IF(J1<>"",J1)))

Thanks again!

 

[fredericofonseca]

Yes there were too many. There should be only one after the J1 e.g. J1,""

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[KenWright]

Another option would be to select the columns containing the names, do Edit / Go To / Special / Blanks, then do Edit / delete / Shift cells left. Once done simply delete all columns aftre the first one with names in.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[AccessGuruCarl]

Not familiar with excel, trying to something similiar.

I have 2 columns Co_ID and SKU I'd like to merge

I'd like to merge the 2 columns and add a dash between.

Ex.. Co_ID SKU

01 56987
31 4576
66 5698


Merged Output

01-56987
31-4576
66-5698

I also will need help in how to make the formula work.

Thanks

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[KenWright]

Assuming your data is in Cols A and B, with headers in row 1 and data starting in row 2. In a third column C, in Cw2 put the following formula:-

=A2&"-"&B2

and then copy the cell and paste down as far as your data goes. You can just hover over the bottom right hand corner of cell C2 once you have selected it and then just double click if you prefer and it will fill down automatically.

Now copy Col C and then do edit / paste special / values and you can now delete columns A and B.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[arthurbr]

Just wanted to add a little trick to paste cells a long way down : faq68-3311

Cheers

 

[KenWright]

Arthur, did you notice that section right in the middle of my note

You can just hover over the bottom right hand corner of cell C2 once you have selected it and then just double click if you prefer and it will fill down automatically.

Regards
Ken........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[AnotherHiggins]

AccessGuruCarl,

In the future, please start a new thread rather than reviving one that has been dormant for many months. It's not a huge deal, but it helps keep things orderly.

Thanks

[tt]_____
[blue]-John[/blue]
[/tt][red]Quidquid latine dictum sit, altum viditur[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[AccessGuruCarl]

Thanks for input guys.

It worked great.

Saved hours of typing.

AccessGuruCarl
Programmers helping programmers
you can't find a better site.