Making a subset

saiko

New Member
Joined
Sep 8, 2002
Messages
15
I have two excel spreadsheets, the first is data imported and formatted from a text file, the second for reporting. The data file contains around 2000 lines of data (such as customer name, code, current balance, birth date, etc). The second file, contains a subset of this file, of around 500 'special' customers. They're the ones I'm interested in.

In the first spreadsheet, all the data is named as a range. I have a macro (in the second spreadsheet), which uses the range and a lookup formula to pick up data for these 'special' customers. "Special' customers are continually added / removed, in the report file.

The pain is that I have to enter data against all 2000 customers in the first spreadsheet, in case they're part of the 'special' group, a lookup is done, data imported, and then the report is emailed to a branch.

How do I pick only customers in the special group, for subsequent data entry? Maybe something like, I need code to read the customers in the second file, then create a new file to parse, or collect only those 500 or so special customers. I can then use this newly-created file to enter supplementary data in, for importing to the report file.

Any help most gratefully appreciated, I need this completed by Monday!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi saiko:

You did refer to using VLOOKUP function -- so unless I am missing something here, how about creating a list of names of your special customers, and then using VLOOKUP to pick up the supplementary information from information in your first spreadsheet.

Would it not what you are looking for?

Regards!

Yogi
 
Upvote 0
Thanks Yogi.

Maybe it would work. Guess I could create a 3rd sheet, to do a lookup from the report file to get my customers, and then use this to input data from the original data file.

Seems messy though. Basically, I export from an accounting database to a text file, wehich is then imported to an excel file. I go through it and enter "y" or "n" against each customer, then my report file picks up selected customers. All I really weant to do is be able to enter data against my selected customers, not all. What do yuo think?

Regards
David R. Neil (saiko)
 
Upvote 0
Hi David:

It should be easy to automate the process of picking up info for your special customers, using VLOOKUP, MATCH, INDEX, etc, or some combination of these or similar functions.

Have you tried it? What result did you get? What result you expect to get? If you can post some sample data from both lists with explanation of what you have tried, what result you got, and what result you would like to get, it will help in clearly understanding the situation and in formulating a solution.

Regards!

Yogi
 
Upvote 0
I read your inquiry...and I have to do something similar on a regular basis....one step that i elimianted was to modify the data by adding the "special" code to the raw data itself....creating a field called "Customer code"....like "1" being an excellect customer....."2" being average...etc....It took a little time to classify, but it was worth it in the long run..Then I have all customers at my disposal and do various pivot tables and on that data.....

hope this helps..

pll
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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
Back
Top