Match specific date horizontally and insert value

[Tuan Lac]

Hi all,

I found this forum very helpful so I sign up and this is my first post in this forum.

Please help! I have attached an excel file to demonstrate the problem.

I have data as below, date from column D will keep going infinity (as a diary record)

Column A B C D E F G H I J
1 Item Qty Delivery date 04/08 05/08 06/08 07/08 08/08 09/08 10/08
2 red 2 04/08
3 blue
4 pink 5 09/08

I would like to have a solution either with Excel Formula or VBA is preferred so that I will be able to match the delivery date on column C to any of the column from D onward and place Qty in the same row.

So for the data example above, As I input Qty "2" in column B2 with the date 04/08 will match date in column "D" and look down same row to input "2" at cell "D2". And so for item pink would result Qty 5 in cell I4.

I hope that would make sense.

Thank you in advance for your time.

 

[bosco_yip]

Try,

In D2, copied across right and down :

=IF($C2=D$1,$B2,"")

Regards
Bosco

 

[Peter Bartholomew]

Avoiding direct references and relative references (standard spreadsheet techniques that I think Bosco prefers)
= IF( (date=deliveryDate), Quantity, "" )
This could be entered as a single array formula (small relatively stable data set) or without it, in which case it will rely upon implicit intersection with the named heading row and delivery data column.
I did not bother about testing the existence of the delivery date because a non-existent date will not match any date in the list.

 

[Tuan Lac]

Hi bosco and Peter,

Thank you for your responses. I realize that using formula will make my file goes really heavy in the long run since every single cell will need to contain it. I have over 300 of items and the date would keep going to the right and therefore the data will eventually grow large. I should have asked only for VBA instead.


Hi all,

VBA will have to carry out two tasks

1. for example, for item "red" when input delivery date and quantity then it will scan through row 2 for a matching date and leave the quantity at the intersecting cell of the same row looking up that date column. Therefore we have the result 2 in cell "D2". In general, VBA will look up value (date format) in column C to match value (also date format) of any column from D onward, find the intersecting of the value current row and matched date, then pick up value in column B (same row) and to insert at the intersect cell.

2. The intersecting insert value will stay as "text" only without formula format since column B and C will be dynamic changes based on user input.

Thank you in advance for your time.