How Would You Do This?

P

Parra

Well-known Member
Joined
Feb 21, 2002
Messages
752
I'm trying to come up with a means to manange some data.

I have 3 sheets of diffrent data, but at the same time they do relate to each other. I want to analize the data but can't figure out a way to do it. I was going to do it in Access but the queries were not producing me what I needed.

Sheet 1: Has Data on When a comercial Ran

Sheet 2: Has Data on when a caller purchased an item after viewing a commerial

Sheet 3: Has Data on When the calls when received.

How could I link all this data together??? But at the same time, keep them seperate to update the new data that comes in.

Thanks
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Is there some sort of unique key that's present in all three sheets? Or how do you know which records in sheet 2 match with those in sheet 1?
 
Upvote 0
There is a date and time when the commercial ran, and a date and time when the customer bought the item BUT the commercial could have ran in any part of the country. So another part of this is linking the data to a DMA via Zip Code.

I was thinking of using a Pivot Table, but how can I grab 3 sets of data and put them into 1?
 
Upvote 0
It's still not clear to me...

If you have a date that a commercial ran and a date when a consumer purchased a product, are you looking for some sort of fuzzy matching on the date? Do you need daily data or can you group into weeks/months/etc?

Perhaps you can use Colo's HTML maker to post a sample of your data and desired results?
 
Upvote 0
Hi Parra,

You can JOIN data tables. For example, in MS Query and using SQL.

So long as the file has been saved and every field has a header. Give the data ranges defined names, but not dynamic. In a case like you described, one way if appropriate to the way you work is to put a worksheet deactivation code on each worksheet. Such as,
Code: 
Private Sub Worksheet_Deactivate()
    Range("A1").CurrentRegion.Name = "Table1"
End Sub

Anyway, when the three data ranges each have a name go via menu Data, Import External Data, New Database Query, Excel, select the workbook, then the fields you want to work with (can be from more than 1 table) and then go into MS Query. You can join fields (drag & drop to do so) graphically or in SQL. This is just a quick intro. Refreshes a little like a pivot table. It is too much to describe here.

Maybe some other posts help?

Count example using multiple criteria (4 fields)

JOIN of two tables to find common records

Summation consolidation of list to new list (not pivot table)

Combine data from multiple sources to one table, using "UNION ALL" One example of joining data from three similar tables - could be for a pivot table.

Parameterised query to grab two lots of records joined by UNION

Extracting records particular records using JOIN, MAX and SUM

TRANSFORM - sample of a cross tab query

If you can code VBA, best to do all this sort of stuff on the fly. Can pull data from closed files, databases, etc., etc.

regards,
Fazza
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
How can I reduce file size of workbook referencing sheets in another workbook?
Replies
4
Views
209
bobsan42
bobsan42
A
  • Question Question
Excel file on SharePoint used at the same time
Replies
1
Views
124
ExcelToDAX
E
D
  • Question Question
Formula "INDIRECT" does not work
Replies
6
Views
217
deri_b
D
D
  • Solved
Could you please help me to write a macro for this situation?
Replies
6
Views
286
jolivanes
jolivanes
I
  • Question Question
Excel/Google Sheet Formula to extract client with unassigned Photographer
Replies
1
Views
194
Rhodie72
Rhodie72

Forum statistics

Threads
1,223,911
Messages
6,175,329
Members
452,635
Latest member
laura12345

Share this page

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