Complicated Excel Lookup Count

Hello,

(Note: I have now amended the post and file).

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.

Here is the file: filev2.xlsx

I have raw data sent to me in a fixed excel format every day (C1:F53) in the attached sheet although the number of rows can vary and number of towns can vary. Columns stays fixed at 4 (C:F). I need to figure out a way for excel to use a flexible table_array to lookup certain dates within that table and return a count into 2 tables (H1:J86) & (L1:N86).


The raw data (C1:C53) in this example whilst always 4 columns in width, number of rows can vary depending on how many dates & towns 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 to populate Table 1 (H1:J86):

a) lookup the code BFSI04 (for example) in cell I1 and match it to Belfast in the mapping table (A4:B34).

b) Then go to the raw data table (C1:C53) and do a count of the "Out Date:" for Belfast and return the count into Table 1 next to the relevant date. The count for each date is in the fourth column in the raw data, "Out Count". So for example the first date in the Belfast raw data is 16/01/2015. The count in "Out count" is 1. So it returns 1. I have populated the table (H1:J86) as I would like it to be after all calculations are done. I have highlighted the cells red for the "Out Dates" as they appear in column C, but also appear in Column D as the first date of pairs.

So what I would like excel to do to populate Table 2 (L1:N86):

a) As above, lookup the code BFSI04 (for example) in cell M1 and match it to Belfast in the mapping table (A4:B34).

b) however this time I need a sum of "In Dates:". The "In Date" in column D I have highlighted in yellow. It will always be below an "Out Date:", highlighted in red as that was when the product went out. In is when it came back. So for example for Belfast 2 products came back on the 26/01/15, so cell M19 in Table 2 has 2. Unfortunately whilst Column F is populated by the report with the count for "Out Date:" the raw data report does not populate a count for the "In Date:" although the "In date" (red) is always going to be below the "Out Date" (yellow) if that helps.

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.


Thank you so much!

Are the number of rows for each town always 24? Or is that what you meant by the number of rows can vary?

Is there a specific town that will always return last on the list? I was able to get column I working, working on column J now, but it is hard if not all the towns are listed.

For reference, paste this into cell I2 and drag down:

Please Login or Register  to view this content.

Do these results at least look correct?

Because of your raw data table is vary and not in uniform, I suggess a helper sheet to give a start. Then let's see what can be done next from the helper sheet.

explain more.

how do you get the orginal data (F45 en F47 are not confirmed)

The tabel H - J are also available (in the original sheet)?

Originally Posted by FlameRetired

Just...

Originally Posted by bebo021999

Because...

Originally Posted by mcmahobt

Are...

Originally Posted by oeldere

explain...

Thank you all for your help I popped into work and have now updated the file in the first post to include the full list of towns and also given a full explanation of what I need to Excel to do to give me the count for "Date In" as well as "Date Out".

I think you guys have helped me more than I could have expected with the "Date Out:" counts and it's pretty much there but the "Date In:" count is very complicated for me to understand as it is always paired up with the "Date Out:" as I have detailed in the first post on this thread.

Again any help really appreciated.

Thanks so much!