In over my head - need to combine rows of information

walt1122

Active Member
Joined
Jun 6, 2002
Messages
318
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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCDEFGHIJ
1
2NameReportNameReportNameReport1Report2Report3Report4
3JohnDoeReport1JohnDoeReport1JohnDoeYesYesYesYes
4JohnDoeReport2JohnDoeReport2JaneDoeNoNoYesNo
5JohnDoeReport3JohnDoeReport3
6JohnDoeReport4JohnDoeReport4
7JaneDoeReport3JaneDoeReport3
8
9
10sourcedata
11filtereddataforuniquenames
12intermediarycomputation
13finalcomputation
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

Active Member
Joined
Jun 6, 2002
Messages
318
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

Active Member
Joined
Jun 6, 2002
Messages
318

ADVERTISEMENT

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

Active Member
Joined
Jun 6, 2002
Messages
318
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

Active Member
Joined
Jun 6, 2002
Messages
318
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
 

Forum statistics

Threads
1,144,275
Messages
5,723,448
Members
422,497
Latest member
dougy99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top