Merging data

[Guest]

Hello,

I would like to be able to merge data from several cells into one, but
without much manual entry. For example, I have data that resembles this:

First Name Last Name Children's Names Sex
Joe Smith Mary F
Joe Smith Greg M
Joe Smith Joey M
Marsha Brown Cindy F
Marsha Brown April F

In this case, I would like to get the following:

First Name Last Name Boys' Names Girls' Names
Joe Smith Greg, Joey Mary
Marsha Brown Cindy, April

There are two main issues: 1) each main identifier (i.e., parent name) may
have different numbers of children (Joe has three kids, Marsha has two), and
2) I want to divide the information based on a criterion (in this example,
"Sex"). I want to aviod having to enter several concatenation formulas
manually, so does anyone have any tips on how to do this? The data is coming
from an Access query, so please let me know if I should be doing this in
Access (and, if so, how?).

Thanks,
Eric

 

[Guest]

If you've got some leeway on presentation format, I can think of a couple
options:

In Access you could use a Cross-Tab Query and display that data in a report
(You should go to the MS Access forum for help with that, though)

In Excel, you could try using a Pivot Table
I got the following results with practically no work:

Last Name...First Name......Sex.......Children's Names..Total
Smith...........Joe................F..........Mary.....................1
.........................................M.........Greg......................
.....................................................Joey......................1
Brown...........Marsha..........F..........Cindy....................
.....................................................April.....................1

Would that meet your needs?