I have to pull some reports for work tonight, and don't want to make things harder than they should be. Admittedly, I'm not an expert in Excel. So if you can help me out, I would appreciate it.

Here's the scenario.

At 8:00pm, I will pull a report that will give all the answers to questions that reps filled in today. For discussion sake, we'll say that in the "A" column, there is the store number, and then each subsequent column gives answer that I need to compile. I can quantify the answers given fairly easily by filtering out the yes/no answers. But there's some where I have to read through comments to quantify responses.

At 9:00pm, I will have to pull a report again that will give me the same answers, just with more stores. So in column "A", there would be all the previous information, plus a new set.

At 10:00pm, I repeat the same process. Now all of information form the first two reports are included with a third set of reports.

Initially, I thought it would just keep all the information in the same order, and just add on the new reports at the end each time I pull a report, but it doesn't do that. It mixes them all up each time.

I'm looking for an easy way to eliminate the data from the first report on that 9:00 report, and then eliminate the data from the first and second on the 10:00pm report. This way, I'm only quantifying a small amount of data each time, and can just add the numbers at the end.

Any help would be appreciated.

is there not a unique identifier in your data set?

derek bomar;1226158 wrote:is there not a unique identifier in your data set?

There is. In the first report, there will be about 75 unique store numbers in column A. In the second report, there will be the same information in column A from the first report, plus an additional 75 unique store numbers. And so on...

so why not just do an If(vlookup), where if the unique # is on both, you can have it say something like "avoid" or "dismiss", and then you can just filter those out...

derek bomar;1226168 wrote:so why not just do an If(vlookup), where if the unique # is on both, you can have it say something like "avoid" or "dismiss", and then you can just filter those out...

Or simply sort by column A after the third report is imported and delete the duplicate store numbers.

derek bomar;1226168 wrote:so why not just do an If(vlookup), where if the unique # is on both, you can have it say something like "avoid" or "dismiss", and then you can just filter those out...

How does one set up a vlookup?

believer;1226172 wrote:Or simply sort by column A after the third report is imported and delete the duplicate store numbers.

Sorting by column A wouldn't be a problem. But going through and unchecking each individual store would be quite the pain in the ass.

sherm03;1226204 wrote:How does one set up a vlookup?

Let's say you have this line of data in one table (rows 1 and 2, columns A, B and C):

ID DATE SCORE
99999 07/15/12 1250

And you have the list of IDs in a separate table, but not the other data. So you want to look up the "SCORE" variable for ID "99999".

You'd enter "=vlookup([cell containing '99999'],table_above!$A$2:$C$2,3,0)". It would return "1250" in this case. It breaks down as follows:

[cell containing '99999'] = the lookup value, the thing you want to find.
table_above!$A$2:$C$2 = the lookup array, the table you want to reference to find your data
3 = the column from the table you want to return (the column containing the matched data is always "1")
0 = what to return if it can't find it

Hope that makes sense - PM me or post here if you have any questions

If the 3rd report contains all the data contained in reports 1 and 2, why not just use report 3 for your analysis?

gorocks99;1226320 wrote:Let's say you have this line of data in one table (rows 1 and 2, columns A, B and C):

ID DATE SCORE
99999 07/15/12 1250

And you have the list of IDs in a separate table, but not the other data. So you want to look up the "SCORE" variable for ID "99999".

You'd enter "=vlookup([cell containing '99999'],table_above!$A$2:$C$2,3,0)". It would return "1250" in this case. It breaks down as follows:

[cell containing '99999'] = the lookup value, the thing you want to find.
table_above!$A$2:$C$2 = the lookup array, the table you want to reference to find your data
3 = the column from the table you want to return (the column containing the matched data is always "1")
0 = what to return if it can't find it

Hope that makes sense - PM me or post here if you have any questions

This...combined with some google searching on the subject...did help greatly. Thank you!

Anna-Town;1226325 wrote:If the 3rd report contains all the data contained in reports 1 and 2, why not just use report 3 for your analysis?

I was under a deadline to get the information in. I wanted to start the report using the first set of data. I had to comb through responses and quantify the data that the reps entered. It took just about an hour for each set of reports. So the idea was, pull the first set of data. Quantify that set of data. Pull the second report. Eliminate the stuff I already quantified. Quantify the new findings from that report. Pull the third report. Eliminate the stuff I already quantified. Quantify the new findings from that report. Then take about 2 minutes to combine all the quantified information into a single recap.

Seeing as how I had to return this by midnight EST, and the last set of data didn't come in until 11pm EST...I didn't have time to just wait on that report and spend about three hours combing through that data.