Hello,
Thanks in advance for your help and to all the contributors here.
I’ve been trying for weeks to get the following to work but have finally given up the ghost and am asking (begging) for help.
I have raw data sent to me in a fixed excel format every day. I need to figure out a complicated way for excel to use a flexible table_array to lookup certain dates within that table and return a count into another table.
I don’t think I can upload anything here but I will try to explain by way of a simplified example below.
This is the table (copied from excel) I need to populate with the SUM of dates from the raw data matching the codes (BFSI04 & BHXW06) in the top two columns.
BFSI04 BHXW06
09/01/2015 1
10/01/2015
11/01/2015
12/01/2015
13/01/2015
14/01/2015
15/01/2015
16/01/2015 1
17/01/2015
18/01/2015
19/01/2015
20/01/2015
21/01/2015
22/01/2015
23/01/2015 1
24/01/2015
25/01/2015
26/01/2015 2
27/01/2015
28/01/2015
29/01/2015
30/01/2015 1
31/01/2015
LOOKUP TABLE (That I created to match the column codes above to the towns in the raw data below)
BFSI04 Belfast
BHXW06 Birmingham
Raw data:
Column 1 Column 2 Column 3 Column 4
Out Date: In Date: Out Number:
Belfast
16/01/2015
16/01/2015 Confirmed
18/01/2015 Check observations
Total day reservations : 1
23/01/2015
23/01/2015 Confirmed
26/01/2015 Check observations
Total day reservations : 1
30/01/2015
30/01/2015 Confirmed
01/02/2015
Total day reservations : 1
Total per station (Belfast) : 3
Birmingham
09/01/2015
09/01/2015 Confirmed
18/01/2015
Total day reservations : 1
26/01/2015
26/01/2015 Confirmed
29/01/2015
26/01/2015 Confirmed
30/01/2015
Total day reservations : 2
Total per station (Birmingham) : 3
So the above raw data whilst always 4 columns in width, number of rows can vary depending on how many dates are in the raw data on a given day.
I have tried to do a lookup but I cannot get the table_array to change dynamically according to how many rows the raw data has for that particular town.
The lookup is also very difficult as there are gaps between the rows.
So what I would like excel to do:
a) lookup the code BFSI04 and match it to Belfast.
Then go to the raw data table and do a count of the "out date" for Belfast and return the count into the first table above. The count for each date is in the fourth column in the raw data, "Out number". So for example the first date in the Belfast raw data is 16/01/2015. The count in "Out number" is 1. So it returns 1. I have populated the first table as I would like it to be after all calculations are done.
I apologise if this appears complicated. Even pointing me in the right direction would be a big help. Is something like this possible using formula or does it need to be VBA based as the lookup table_array needs to change its height everyday. I have tried everything but I am at a loss.
I also need to do a count of the "In Date:" column but one step at a time, I would like to see if the initial problem can be done in excel. .
Thank you so much!
Bookmarks