Complicated Excel Lookup Count - Any help appreciated

[deejaynv]

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!

[bebo021999]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[sourabhg98]

hello
I have understood your problem slightly
So can you please upload the excel file....

[deejaynv]

I will start again as I see the text above is not correct thanks for the help!

[deejaynv]

hello
I have understood your problem slightly
So can you please upload the excel file....

Hello sir I have uploaded the excel file but started a new thread called "Complicated Excel Lookup Count".

Many thanks for your help. Moderator please could you delete this thread?