In over my head - need to combine rows of information

[walt1122]

Hi All, I don't have a clue on how to do this so If anyone can help me out I would greatly appreciate it.
The problem is I receive data from 4 different databases. Each is formatted differently ( capitalizations, field size, etc.) I can take care of all that but the problem is now I have up to 4 rows of the same customer information. Each row contains the customer name and address. In addition each row has for the first field a particular entry that shows what report the person is to receive.I.E., report 1, report 2, report 3, and report 4.

I would like to be able to:
1. Just have one name and address row showing what reports they should get. This way the people who will be doing the mailing can look at the one row entry and get the corresponding reports together in one envelope and mail.
2. long term I would like to be able to "mail merge" the report documents and send a file with the one line summary of what reports each individual shoud get.
3. longer term I need to be able to email them using some distribution list.
Just mentioning this "just-in-case"

So for example: John Q. Public is show on the spreadsheet 4 times because he should get 4 reports called report 1, report 2, report 3, and report 4. What I would like to see is one line for John Q. Public showing the reports needed on the one line John Q. Public (report 1, report 2, report 3, and report 4). Or Jane Doe who should only need to see Report 3, just have Jane Doe (report 3). Hope you get the idea. cause I don't.

thanks

Walt

 

[Yogi Anand]

On 2002-10-03 15:02, walt1122 wrote:
Hi All, I don't have a clue on how to do this so If anyone can help me out I would greatly appreciate it.
The problem is I receive data from 4 different databases. Each is formatted differently ( capitalizations, field size, etc.) I can take care of all that but the problem is now I have up to 4 rows of the same customer information. Each row contains the customer name and address. In addition each row has for the first field a particular entry that shows what report the person is to receive.I.E., report 1, report 2, report 3, and report 4.

I would like to be able to:
1. Just have one name and address row showing what reports they should get. This way the people who will be doing the mailing can look at the one row entry and get the corresponding reports together in one envelope and mail.
2. long term I would like to be able to "mail merge" the report documents and send a file with the one line summary of what reports each individual shoud get.
3. longer term I need to be able to email them using some distribution list.
Just mentioning this "just-in-case"

So for example: John Q. Public is show on the spreadsheet 4 times because he should get 4 reports called report 1, report 2, report 3, and report 4. What I would like to see is one line for John Q. Public showing the reports needed on the one line John Q. Public (report 1, report 2, report 3, and report 4). Or Jane Doe who should only need to see Report 3, just have Jane Doe (report 3). Hope you get the idea. cause I don't.

thanks

Walt

Hi Walt:

Using your sample data, I first filtered the source data to get the unique names, then I concatenated the Name and report as an intermediary, and then finally I used the IF function and the VLOOKUP function to come up with a single line entry per name with the corresponding report.

See the worksheet simulation ...

Book1
	A	B	C	D	E	F	G	H	I	J
1
2	Name	Report		NameReport		Name	Report1	Report2	Report3	Report4
3	JohnDoe	Report1		JohnDoeReport1		JohnDoe	Yes	Yes	Yes	Yes
4	JohnDoe	Report2		JohnDoeReport2		JaneDoe	No	No	Yes	No
5	JohnDoe	Report3		JohnDoeReport3
6	JohnDoe	Report4		JohnDoeReport4
7	JaneDoe	Report3		JaneDoeReport3
8
9
10				sourcedata
11				filtereddataforuniquenames
12				intermediarycomputation
13				finalcomputation
14
Sheet3

</SPAN>

Please post back if it works for you -- otherwise explain a little further and let us take it from there.

Regards!

Yogi

 

[walt1122]

Hi Yogi, thanks for getting back to me. Maybe it's me, don't know but all I am able see is the simulation of the resulting formulas not the actual process. To be honest I know only have the most rudimentary understanding of IF STATEMENTS or how to use the VLOOKUP. Any chance you can show me a sample of them as they relate to this project. In addition I must confess the file is rather large. I have already had to break it out into individual letters of the alphabet. The entire set of records are over 365,000 lines of data. Even after doing the alphabet thing, I'm having a hard time. System keeps locking up when I do sorts or subtotals.

thanks

Walt

 

[Yogi Anand]

Hi Walt:

I am going to try to give you a step by step explanation on what my simulation shows:

1. source data is in cells A2 through B7
2. Then I inserted an intermediary column D, where D2 has the formula =A2&B2 -- this concatenates the name and the report
3. Then I used Advanced Filter ... DATA|FILTER|ADVANCED_FILTER to extract unique records from the source data, and I output the resulting unique records programatically in cells F3:F4
4. Now that we have uniquely the names of people who are going to receive the reports -- let us look at what reports they are going to get -- for that I used the following formula in cell G3

=IF(ISNA(VLOOKUP($F3&G$2,$D$3:$D$7,1,FALSE)),"No","Yes")

this formula says, if the associated recipient is going to get Report1, key-in "Yes", otherwise "No"

This formula is then copied to cells H3:J3 and G4:J4

to get the final tally of who are the recipients and what reports they are going to get.

You may have to study it piece by piece -- first how ADVANCED FILTER is used to extract only the unique names; how and why the names and reports are being concatenated; and finally how the formual in cells G3 to J4 using VLOOKUP, IF, ISNA, ... functions is getting us the results that we need.

I hope this long winded explanation helps a bit.

Regards!

Yogi

 

[walt1122]

Yes Yogi, that helps a lot. I think I can work with it now. I think I understand the concepts as you explained them and should have little trouble changing the formula fields to get it to work.

Thank you so much.

Walt

 

[walt1122]

Hello Again Yogi, As I suspected, the file is just too big? Using the Advanced Filter triggers Dr. Watson and locks up my system. In this case I'm trying to work with 10,000 lines of data and I guess it is too much for my system.

But thanks again I'm sure if I figure out what is causing the file to crash your formulas will work

Walt

 

[walt1122]

Hi All, Yogi was able to help me with the original problem THANKS YOGI, But now I'm being asked to redo the end result. After getting the unigue names and doing the "YES", "NO" for seeing who gets what report, they want me to build a matrix to combine the different possible variables. So what I need is to have some method of listing/displaying If a person is to get report 1 and 3 then all people who are also are to get report 1 and 3 are defined as one group I.E group A, while all people who are to get report 3 and 5 are another group I.E. group B, and those people who are to get report 1 and 2 and 5 are another group I.E. group N (get the picture?). The purpose is to have the different reports sent via email to those people who should only receive the reports from the list/group they are in. So if I know group A only gets reports 1 and 3 I can put them in an email and group mail just those reports to just those people. So you statistics people, I don't even know how to figure out how many possible 'groups" there are let alon how to do it.
Any help would be appreciated

thanks

Walt