Outputrows, empty columns

[PatrickK]

Hello.

I have a problem that I can not solve. I am not VBA-savvy but more of av excelnoob, nevertheless I got it...

We have an excelsheet with the data (a lokal adressbook) that contains names and adresses. Columns and rows look like this:

Housenumber SurName1 FirstName1 SurName2 FirstName2 phone
10 Jones Jill Peter 123456
12 Doe Jen Brown Dean 234567
14 Smith Bella 345678
...

What we see is that some people are married, some are not and some are singles. Some are null depending on marital (?) status.

If they are married I need to have an outputrow that looks like this:
No 10, Jill & Peter Jones, 123456
If they are not married I need to have an output row that looks like this:
No 12, Jen Doe & Dean Brown, 234567
If they are single I need to have an outputrow that looks like this:
No 14, Bella Smith, 345678

I have tried to do some IF-statements but they get heavily nested.
Please help...

/Pat

 

[jbeaucaire]

Housenumber SurName1 FirstName1 SurName2 FirstName2 phone
10          Jones    Jill                Peter      123456
12          Doe      Jen        Brown    Dean       234567
14          Smith    Bella                          345678
...

What we see is that some people are married, some are not and some are singles. Some are null depending on marital (?) status.[/code]

Where do we see that?

 

[HalfAce]

Not sure if you're looking for a formula or vba solution so here's a formula you can try:

=IF(COUNTA(D2:E2)=0,"No "&A2&", "&B2&" "&C2 & " " & F2,IF(COUNTA(D2:E2)=1,"No "&A2&", "&C2&" & "&E2&" "&B2 & " " & F2,IF(COUNTA(D2:E2)=2,"No "&A2&", "&C2& " " & B2 & " & "&E2&" "&D2&" "&F2)))

Does this help?

 

[PatrickK]

Where do we see that?

Hmm, i see that it is not obvious...
If there is two persons in the same house with the same name -> married
If there is two persons in the same house without the same name ->not married
Only one name -> single, divorced, widow, etc.

 

[jbeaucaire]

Wow, ok, that is assuming a lot based on names only.

Same name.... brother/sister, father/daughter, mother/son, etc.... including husband/wife
Diff names... can still be married.

Anyway, if it's as straightforward as your example, HalfAce's approach should suffice. This is the same thing, just without the "IF"...

=CHOOSE(COUNTA(D2:E2)+1, "No "&A2&", "&B2&" "&C2 & " " & F2, "No "&A2&", "&C2&" & "&E2&" "&B2 & " " & F2, "No "&A2&", "&C2& " " & B2 & " & "&E2&" "&D2&" "&F2)

 

[HalfAce]

Yeah, I like that Choose formula. Especially like the way you color coded each condition. I may have to start doing that. (Sure makes it easier to decipher.)

 

[jbeaucaire]

Hehe, sometimes my eyes cross on some formulas posted here on the forum, have to forensically analyze them looking for the logic breaks. The & & & & stuff actually made me laugh it's so tricky to read.

 

[PatrickK]

Wow, ok, that [...text and code...]

Thanks a lot, it worked fine.

EDIT:
And the point is not if they are married or not, it is just a list of the recidents in our area that we print and distribute to them.

//P

 

[mikerickson]

How about
=House & TRIM(CONCATENATE(FirstName2," ",SurName2," & ",FirstName1," ",SurName1)) & Phone