Getting Excel to reconcile two types of data
February 12, 2010 11:27 AM Subscribe
Excel Filter: Say I have a spreadsheet with two types of data. a) list of transactions with date/time + transaction, and b) days with traffic. How do I reconcile the two sets of data swiftly & easily?
posted by Muffy to Computers & Internet (8 answers total) 1 user marked this as a favorite
I have two types of data. One is a line-by-line list of transactions and the exact date/time they occurred on - one transaction per line, so one date can span across multiple rows. The other is traffic data - visits per day - one day per line.
Normally I reconcile these by going through the tedious task of tallying the transactions into a # of transactions per day spreadsheet. This involves lots of IF statements and adding things together, then removing all the rows that aren't the tally for that date & then adding in any dates that have zero transactions. It's all rather manual. Since I generate this report once a week, I'd much rather paste the data in & allow the formulas to do the matching for me.
Example (using commas to represent colums)
sale date, transaction ID (SKU), traffic date, traffic
1/1/2010, 1, 1/1/2010, 500
1/1/2010, 1, 1/2/2010, 700
1/1/2010, 1, 1/3/2010, 900
1/2/2010, 1, 1/4/2010, 850
etc. - as you can see there are 3 transactions on the first day and it takes up 3 lines, but the traffic stats are one day per line. I normally create an extra column with this formula [ =IF(A2=A1,C1+1,1) ] to tally each day's transactions and copy/paste into notepad & back into EXCEL to turn the formula values into hard numbers, and then delete the lower numbered duplicate date lines.
Is there a way to have Excel just reconcile these two bits of data? If this is possible, I'd imagine the logic would be something like this:
Column A = transaction date
Column B = transaction ID (what was sold)
Column C = tally of total products sold that day (if A2<A1, then C1+B2, else 1 - or something like this, a formula like this will keep adding +1 each time a transaction occurs, but reset at the start of a new day)
Column D = date
Column E = # of visitors on that date
Column F = the mythical column where my new formula appears that shows the # of transactions on date
Column F would have something like:
"show me the cell in column C that corresponds to the cell in column A that has the highest date that's equal to or less than the date in D (and if there is no date equal to D, then show zero)"
"show me the highest cell in column C that has a date in column A that corresponds to the date in column D, otherwise (if there is no equal date), show zero."
or, eliminating the need for column C
"Tally all cells in column B where the date in column A matches the date in column D, otherwise show 0."
Or am I just stuck doing this by hand?