Excel Problem 2

[frankcpl]

I am working on an excel worksheet that I need some help on. I will try to explain it the best I know how. The worksheet has column a for assignment(apartment), which could have 2 to 6 occupients. Column b is the last name of said occupients. Column c is first name of said occupients and column d is roomates of said occupients. What I am wanting to do is combine the roomates name in column d, e, f, ect until end of like apartments. If column a has 2 apartment numbers that match, then column d would show the names merged in the same cell.
Assingment Last Name First Name Roomate1 Roomate2
BEN A11 Bonner Kathryn
BEN A11 Roberts April
BEN A13 Ferguson Morgan
BEN A13 Williams Jessica
BEN A14 Coghlan Tara
BEN A14 Hill Brittany
BEN A14 Reeder Jessica
BEN A14 Salinas Beatrice
BEN A14 Saxton Shelby

The end result for BEN A11 would be:
BEN A11 Bonner Kathryn Bonner, Roberts,
ANy help would be greatly appreciated.

 

[lionelhill]

Don't forget Gruuu's mail about SQL.

Depending on why you want the data, restructuring the data this way may be really unhelpful downstream (for example, if roommate1 moves out, you'll need to start shuffling data sideways. If you want to find the room occupied by Joe Bloggs you will need to search multiple columns because you don't know if he's roommate1 or roommate2 etc..

Keeping the data in one-row-per-person form allows you to fish out almost any combination of data using SQL queries. Ideally if you're making a database, it'd be better to use database software (Access etc.).

Given the opportunity to write a nice juicy macro, most people will do so. But it's worth checking that it makes sense to carry out the task before writing the macro to do it.

 

[frankcpl]

Lionelhill,

That does make since. The data that I am using is for a campus dorm. It isn't for a huge scale thing. The way it works is I get an excel sheet that has everyones infor and room numbers. I then do a mail merge. What I am trying to do is simplify the mail merge by gathering all the information on one row so each person receives a letter and they know who their roommates are and the phone numbers for each roommate. There is only 1000 assignments, so I figured I received in in excel, why not do a quick macro to sort the data out. I am not the best database person, but for the use I am needing this information, I think a building a database would be overkill. I may be worng though.

Thanks again for the input.

 

[frankcpl]

Skip,

I am working with your code and I am getting a Label not found and this part is highlighted:
GoSub NextRow
I have double checked and the code is letter for letter to what you typed. Any Ideas?

Frankcpl

 

[frankcpl]

Label not defined. sorry for the mis wording on error.

 

[SkipVought]

I am working with your code and I am getting a Label not found and this part is highlighted:
GoSub NextRow

Do you have this code in your procedure, at the bottom?

    Exit Sub[b]
NextRow:[/b]
    lRow = lRow + 1
    If iMaxCol < iCol Then iMaxCol = iCol
    iCol = 1
    sPrev = r.Value
    wsSUM.Cells(lRow, iCol).Value = sPrev
    Return
End Sub

go back and COPY all the code and PASTE it into your module.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[frankcpl]

I will recopy everything again. I do have that at the bottom of the code. Will let you know in a minute or two.

 

[frankcpl]

Found the problem, I had end sub before the NextRow. Thanks for the help. I also have been trying to figure out how to remove the comma before the phone number. It is displaying it with:

LName, FName, 999999999

Which is fine if that is what I have to do, but thinking about grammer on the letters I am going to merge this information too.

 

[SkipVought]

What character would you want instead of a comma?

What other columns of data will you be concatenating?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[frankcpl]

Skip,

I was able to step through you code and found where it was entering the comma. I like to have the comma between the last name and first name only, but after looking at the code, it will put the comma behind all the data present. I don't see an easy way to change it so I am just going to remove the comma altogether. I would like the end result to look like this:

Roommate1 ect...
Doe, Jane 9999999999 ect...

Your code really does the trick with not errors in it. Just trying to get it grammatically correct now.

Frankcpl

 

[SkipVought]

I asked two questions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[frankcpl]

My apologies. I like the comma between the last and first name only.

As for other columns of data will I will be concatenating the only data is the address of each person, but it isn't concatenated. It is just for the use of the mail merge.

Thanks again

 

[SkipVought]

The data is concatenated in the code.

Try this and see if it works for you...

        With wsSUM.Cells(lRow, iCol)
            For c = 2 To colLIM[b]
                Select Case c
                    Case 4
                        .Value = Left(.Value, Len(.Value) - 2) & " " & r.Offset(0, c - 1) & ", "
                    Case Else
                        .Value = .Value & r.Offset(0, c - 1) & ", "
                End Select[/b]
            Next
            .Value = Left(.Value, Len(.Value) - 2)
        End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[frankcpl]

Skip you make it look easy. One last question. How do you control where to start coping the information from for the roommates.

The only reason I ask is because I have the address I have to insert after assignments for the mail merge, but don't want it concatenated with the other data.

Thanks a ton.

 

[SkipVought]

I have been assuming that they are ALL room mates. Is this not correct?

If not how are they different?

Do they ALL have the same information (last, first, phone, ???????) or not?

I do not understand, "I have the address I have to insert after assignments for the mail merge, but don't want it concatenated with the other data."

Please DEMONSTRATE what that means with a concrete example that is consistent with your posted example.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[frankcpl]

Everything you have supplied works just as you state and fullfills what I am needing. I have to mail each individual a letter, inside which will have the roommates with a phone number for each roommate. I don't want it concatednated with anything else because the address is a home address for each peron.

As I stated your code works just like I need it to. Being that I am not fimilar with the terms you used, I am unable to see where to make changes.

Assignment LName FName Phone#

This is complete and works wonderful. Due to trying to get information concatenated with each other, I forgot I was working a mail merge problem. So column e thru h (for instance, could be b thru e)will be the individual students home address (street, city, state, zip), which will not be concatenated with anything.

 

[zelgar]

Frank, The reason why you're getting a type mismatch when you modified my code to :

Range(Chr$(67 + roommate) + Trim(Str(x))) = Range("b" + Trim(Str(y))) + ", " + Range("c" + Trim(Str(y))) + " Phone: " + Range("d" + Trim(Str(y)))

the data in Cols B & C are Text. Are the phone numbers Column D text or numbers? If Numbers, you'll be getting a Type Mismatch Error
Here's what you should do:

phone$ = Format(Range("d" + Trim(Str(y))), "###-###-###")
Range(Chr$(67 + roommate) + Trim(Str(x))) = Range("b" + Trim(Str(y))) + ", " + Range("c" + Trim(Str(y))) + " Phone: " + phone$

Additionally, you may wish to adjust the lines using the CHR$ function. CHR$(65)=A, 66=B... Right now, the CHR$(67+roommate) will start populating everything in the D column. If you want to start populating in Col E, Change the 67 to 68

 

[SkipVought]

Ahhhhh. Here's the requirement!

I have to mail each individual a letter, inside which will have the roommates with a phone number for each roommate. I don't want it concatednated with anything else because the address is a home address for each peron.

So is the FORMAT (as specified above) important, or is the transfer of proper information important?

It ssems the latter is more important than the former.

Then consider this....
[tt]
1. the ASSIGNMENT table on a sheet named Assignments

Assingment Last Name First Name
BEN A11 Bonner Kathryn
BEN A11 Roberts April
BEN A13 Ferguson Morgan
BEN A13 Williams Jessica
BEN A14 Coghlan Tara
BEN A14 Hill Brittany
BEN A14 Reeder Jessica
BEN A14 Salinas Beatrice
BEN A14 Saxton Shelby

2. the PEOPLE table on a sheet named People

Last Nam First Name Phone Address
Bonner Kathryn 999-999-0001 123 Main St, Anytown, ZZ 12345
Roberts April 999-999-0002 124 Main St, Anytown, ZZ 12345
Ferguson Morgan 999-999-0003 125 Main St, Anytown, ZZ 12345
Williams Jessica 999-999-0004 126 Main St, Anytown, ZZ 12345
Coghlan Tara 999-999-0005 127 Main St, Anytown, ZZ 12345
Hill Brittany 999-999-0006 128 Main St, Anytown, ZZ 12345
Reeder Jessica 999-999-0007 129 Main St, Anytown, ZZ 12345
Salinas Beatrice 999-999-0008 130 Main St, Anytown, ZZ 12345
Saxton Shelby 999-999-0009 131 Main St, Anytown, ZZ 12345

3. the results using MS Query (no vba) Data > External Data > Excel Files -

Assingmen Last Name First Name Phone Address
BEN A11 Bonner Kathryn 999-999-0001 123 Main St, Anytown, ZZ 12345
BEN A11 Roberts April 999-999-0002 123 Main St, Anytown, ZZ 12345
BEN A11 Bonner Kathryn 999-999-0001 124 Main St, Anytown, ZZ 12345
BEN A11 Roberts April 999-999-0002 124 Main St, Anytown, ZZ 12345
BEN A13 Ferguson Morgan 999-999-0003 125 Main St, Anytown, ZZ 12345
BEN A13 Williams Jessica 999-999-0004 125 Main St, Anytown, ZZ 12345
BEN A13 Ferguson Morgan 999-999-0003 126 Main St, Anytown, ZZ 12345
BEN A13 Williams Jessica 999-999-0004 126 Main St, Anytown, ZZ 12345
BEN A14 Hill Brittany 999-999-0006 127 Main St, Anytown, ZZ 12345
BEN A14 Reeder Jessica 999-999-0007 127 Main St, Anytown, ZZ 12345
BEN A14 Salinas Beatrice 999-999-0008 127 Main St, Anytown, ZZ 12345
BEN A14 Saxton Shelby 999-999-0009 127 Main St, Anytown, ZZ 12345
BEN A14 Coghlan Tara 999-999-0005 127 Main St, Anytown, ZZ 12345
BEN A14 Saxton Shelby 999-999-0009 128 Main St, Anytown, ZZ 12345
BEN A14 Coghlan Tara 999-999-0005 128 Main St, Anytown, ZZ 12345
BEN A14 Salinas Beatrice 999-999-0008 128 Main St, Anytown, ZZ 12345
BEN A14 Hill Brittany 999-999-0006 128 Main St, Anytown, ZZ 12345
BEN A14 Reeder Jessica 999-999-0007 128 Main St, Anytown, ZZ 12345
BEN A14 Coghlan Tara 999-999-0005 129 Main St, Anytown, ZZ 12345
BEN A14 Hill Brittany 999-999-0006 129 Main St, Anytown, ZZ 12345
BEN A14 Salinas Beatrice 999-999-0008 129 Main St, Anytown, ZZ 12345
BEN A14 Reeder Jessica 999-999-0007 129 Main St, Anytown, ZZ 12345
BEN A14 Saxton Shelby 999-999-0009 129 Main St, Anytown, ZZ 12345
BEN A14 Hill Brittany 999-999-0006 130 Main St, Anytown, ZZ 12345
BEN A14 Salinas Beatrice 999-999-0008 130 Main St, Anytown, ZZ 12345
BEN A14 Coghlan Tara 999-999-0005 130 Main St, Anytown, ZZ 12345
BEN A14 Reeder Jessica 999-999-0007 130 Main St, Anytown, ZZ 12345
BEN A14 Saxton Shelby 999-999-0009 130 Main St, Anytown, ZZ 12345
BEN A14 Saxton Shelby 999-999-0009 131 Main St, Anytown, ZZ 12345
BEN A14 Coghlan Tara 999-999-0005 131 Main St, Anytown, ZZ 12345
BEN A14 Salinas Beatrice 999-999-0008 131 Main St, Anytown, ZZ 12345
BEN A14 Hill Brittany 999-999-0006 131 Main St, Anytown, ZZ 12345
BEN A14 Reeder Jessica 999-999-0007 131 Main St, Anytown, ZZ 12345
[/tt]
This is GROUPED on ASSIGNMENT & ADDRESS.

Mail each of these Groups.

This is the SQL code for the query...

SELECT A1.Assingment, A.`Last Name`, A.`First Name`, P.Phone, P1.Address

FROM
  `Assignments$` A
, `People$` P
, `Assignments$` A1
, `People$` P1

WHERE A.`First Name` = P.`First Name`
  AND A.`Last Name` = P.`Last Name`
  AND A.Assingment=A1.Assingment
  AND A1.`First Name` = P1.`First Name`
  AND A1.`Last Name` = P1.`Last Name`

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}