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.
[TABLE="width: 212"]
<tbody>[TR]
[TD][/TD]
[TD] BFSI04[/TD]
[TD] BHXW06[/TD]
[/TR]
[TR]
[TD="align: right"]09/01/2015[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]10/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16/01/2015[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23/01/2015[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26/01/2015[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: right"]27/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/01/2015[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 227"]
<tbody>[TR]
[TD="colspan: 2"]LOOKUP TABLE (That I created to match the column codes above to the towns in the raw data below)[/TD]
[/TR]
[TR]
[TD]BFSI04[/TD]
[TD]Belfast[/TD]
[/TR]
[TR]
[TD]BHXW06[/TD]
[TD]Birmingham[/TD]
[/TR]
</tbody>[/TABLE]
Raw data:
[TABLE="width: 633"]
<tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Column 3[/TD]
[TD]Column 4[/TD]
[/TR]
[TR]
[TD]Out Date:[/TD]
[TD]In Date:[/TD]
[TD][/TD]
[TD]Out Number:[/TD]
[/TR]
[TR]
[TD]Belfast[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]16/01/2015[/TD]
[TD][/TD]
[TD]Confirmed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]18/01/2015[/TD]
[TD][/TD]
[TD]Check observations[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total day reservations :[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]23/01/2015[/TD]
[TD][/TD]
[TD]Confirmed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]26/01/2015[/TD]
[TD][/TD]
[TD]Check observations[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total day reservations :[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30/01/2015[/TD]
[TD][/TD]
[TD]Confirmed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]01/02/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total day reservations :[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total per station (Belfast) :[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Birmingham[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]09/01/2015[/TD]
[TD][/TD]
[TD]Confirmed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]18/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total day reservations :[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]26/01/2015[/TD]
[TD][/TD]
[TD]Confirmed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]29/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]26/01/2015[/TD]
[TD][/TD]
[TD]Confirmed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total day reservations :[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total per station (Birmingham) :[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
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!
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.
[TABLE="width: 212"]
<tbody>[TR]
[TD][/TD]
[TD] BFSI04[/TD]
[TD] BHXW06[/TD]
[/TR]
[TR]
[TD="align: right"]09/01/2015[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]10/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16/01/2015[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23/01/2015[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26/01/2015[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: right"]27/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30/01/2015[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]31/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 227"]
<tbody>[TR]
[TD="colspan: 2"]LOOKUP TABLE (That I created to match the column codes above to the towns in the raw data below)[/TD]
[/TR]
[TR]
[TD]BFSI04[/TD]
[TD]Belfast[/TD]
[/TR]
[TR]
[TD]BHXW06[/TD]
[TD]Birmingham[/TD]
[/TR]
</tbody>[/TABLE]
Raw data:
[TABLE="width: 633"]
<tbody>[TR]
[TD]Column 1[/TD]
[TD]Column 2[/TD]
[TD]Column 3[/TD]
[TD]Column 4[/TD]
[/TR]
[TR]
[TD]Out Date:[/TD]
[TD]In Date:[/TD]
[TD][/TD]
[TD]Out Number:[/TD]
[/TR]
[TR]
[TD]Belfast[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]16/01/2015[/TD]
[TD][/TD]
[TD]Confirmed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]18/01/2015[/TD]
[TD][/TD]
[TD]Check observations[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total day reservations :[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]23/01/2015[/TD]
[TD][/TD]
[TD]Confirmed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]26/01/2015[/TD]
[TD][/TD]
[TD]Check observations[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total day reservations :[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30/01/2015[/TD]
[TD][/TD]
[TD]Confirmed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]01/02/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total day reservations :[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total per station (Belfast) :[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Birmingham[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]09/01/2015[/TD]
[TD][/TD]
[TD]Confirmed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]18/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total day reservations :[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]26/01/2015[/TD]
[TD][/TD]
[TD]Confirmed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]29/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]26/01/2015[/TD]
[TD][/TD]
[TD]Confirmed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30/01/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total day reservations :[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Total per station (Birmingham) :[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
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!
