Posted by Skyhook
on Wed 22 Sep 10, 9:25 PM to Skyhook's blog.
From Mrs S:
I need to get this sorted by tomorrow or my head's on the block... getting nowhere with finding a solution on t'web, so wondered if any of you lovely IC bods had any ideas.
I have two spreadsheets with different data on the same customers. The first, Book1, has the customer number in column B. The second, Book2, has the customer number in column C. Book2 only has customers that have bought from us this year, while Book1 is all customers from the last couple of years. So not all Book1 customers will be in Book2.
At the same time, Book2 has multiple entries for each separate customer, as each order is a separate entry. The spend amounts are in column I. So for example, Joe Bloggs might have 3 entries in Book2.
What I want to create is a new spreadsheet, Book3, which has columns A - E inclusive exactly as in Book1 (so long as the same customer also has entries in Book2) and column I from Book2 as column F for Book3.
I'd also like the spend amounts for each customer to be added together, so I have one line per customer in Book3 with their total spend in column F, but I can do this separarely if necessary.
Something like this:
A: Country B: Customer # C: Customer name D: Contact # E: Contact name F: Total spend
Book1 looks like this:
A: Country B: Customer # C: Customer name D: Contact # E: Contact name
Book2 looks like this:
A: Notes B: Country C: Customer # D: Customer name E: Contact name F: Position G: Contact # H: Street address I: Order amount
As the contact names and some spellings of company names do differ slightly, I want to take that information only from Book1 and ignore Book2. I don't want entries where there is no matching Customer # in Book2.
There are nearly 17 thousand of entries in Book1 and over 34 thousand entries in Book2, if I had to do this by hand I'll still be here in about March next year....
Any and all suggestions gratefully received.
Help!
| 22 Sep 10, 9:31 PM littlenic 5 yrs |
The starting point for this is the wonderful world of VLOOKUP, though it will mean on Book 2 you also want to copy col C and paste it as col A also. Then, using a v lookup you can tell your spreadsheet to go off and for every customer on Book 1, go find it on book 2, and bring back whichever columns of data you want. Personally, I'd probably rationalise the spends first, which you could be extremely fancy and do via some kind of add thing until customer != customer. Or, you know, manually. But yeah. VLOOKUP is the answer. Or at least, that's the way I'd do it, I'm sure knowing Excel there'll be more than one way! Edited 22 Sep 10, 9:32 PM by littlenic | |
| 22 Sep 10, 10:32 PM tallulahme UK, 2 yrs  |
Bye bye head! Do you have to be the ice queen intellectual or the slut whore? Isn't there some way to be both? - Susan Saranden | |
| 22 Sep 10, 10:47 PM Top_Class UK(GU), 2 yrs |
If you've Access then export each Excel sheet (Book) as CSV and read into Access as a table (one table per sheet) then form a simple query joined on customer reference number and once executed export the query table as CSV and read back into Excel as Book3. Access's Query Wizard allows you to construct the query without SQL knowledge on a pure point-and-click interface. But, roughly speaking this is what it'd be: Select fields A, B, C, D, E from Table Book_1 and fields C, I from table Book_2 with Table_1 inner joined on Table_2 where Table Book_1 field B equals Table Book_2 field C. The same sort of problem you have is shown being solved here in terms of SQL tables via SQL queries. The CSV (Comma Separated Values) file format simply transports data easiliy between Access and Excel (import/export data) where automation is not present (ie manually). ETA :: This is Access's actual SQL query constructed by the query wizard (using the procedure I described above to get the Excel sheets into Access as tables): SELECT [IC Sheet1].[Col-A], [IC Sheet1].[Col-B Cust Ref], [IC Sheet1].[Col-C], [IC Sheet1].[Col-D], [IC Sheet1].[Col-E], [IC Sheet2].[Col-I Spend] FROM [IC Sheet1] INNER JOIN [IC Sheet2] ON [IC Sheet1].[Col-B Cust Ref] = [IC Sheet2].[Col-C Cust Ref]; The only thing I'd say is that, having done it all, is that you can move the data between applications in native XLS files and not worry about CSV at all. "Fork handles?" "No, not 'fork handles' ... four candles." Edited 22 Sep 10, 11:26 PM by Top_Class | |
| 22 Sep 10, 10:50 PM Needs2pls 2 yrs |
The suggestion to use Access is a good one - it will do the joins very quickly - I would guess its only half and hours work if you now how to use Access. | |
| 22 Sep 10, 10:54 PM Needs2pls 2 yrs |
And Top Class's description of what to do is good - but when you do the query, don't forget to put totals on and sum the values while grouping on the others to get the total spend information | |
| 23 Sep 10, 12:21 AM TheGit UK(CH), 2 yrs |
I can appreciate @Top_Class 's solution. A database would be a much more efficient tool for this job. However within the limitations of using Excel one solution to filter Sheet1 based on Sheet2 would look something like this:
for the customer number column in Book3. This is not the most rapid of processes as it entails a VLOOKUP on 30 odd thousand record performed 16 odd thousand times (i.e. nearly 500,000,000 comparisons). Given modern computers it should take about 5 mins to run. The other columns for Book3 are fetched from Book1 only if the customer number column (calculated above) is not blank. The total spend can be calculated with the SUMIF function using the afore mentioned customer number applied as the criteria to the customer number column as the range and the order amount column as the the sum range. Again this could be better, 500,000,000 more comparisons, 5 more minutes. This is all assuming that each customer number is only represented once in Book1, with multiple instances in Book2. Hope I've guessed your data correctly.
Hope this helps. I'm here to help | |
| 23 Sep 10, 12:44 PM tanken UK(NR), 2 yrs |
This seems the least complicated solution
'Kiss the boot of shiny, shiny leather' - Velvet Underground |
