Presenting Data - Matching two variables with two reference points + offset

Luke777

Board Regular
Joined
Aug 10, 2020
Messages
213
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I'll present my three worksheets first and explain afterwards. I think showing the starting condition and the end condition makes it easier to show what I'm after.

Book2
ABCDEFG
1Part # >1010104325023984511289
2Time Slot
306/01/2022 06:3006/01/2022 06:45
406/01/2022 06:4506/01/2022 07:00
506/01/2022 07:0006/01/2022 07:15
606/01/2022 07:1506/01/2022 07:30
706/01/2022 07:3006/01/2022 07:45
8
907/01/2022 06:1507/01/2022 06:30
Unfinished


Book2
ABC
110101006/01/2022 06:30Rad
24325006/01/2022 06:30Chickens
32306/01/2022 06:3042
410101006/01/2022 07:00Batman
59845106/01/2022 07:00A Hat
6128906/01/2022 07:00Richard Hammond
7128906/01/2022 07:30Flamingo
8
92307/01/2022 06:15Sprouts
Source


Book2
ABCDEFG
1Part # >1010104325023984511289
2Time Slot
306/01/2022 06:3006/01/2022 06:45RadChickens42
406/01/2022 06:4506/01/2022 07:00
506/01/2022 07:0006/01/2022 07:15BatmanA HatRichard Hammond
606/01/2022 07:1506/01/2022 07:30
706/01/2022 07:3006/01/2022 07:45Flamingo
8
907/01/2022 06:1507/01/2022 06:30Sprouts
Final Form


The times in B of Unfinished/Final Form can be ignored as far as I understand.

To create the "Final Form" I am simply looking at the part # and Time Slot in A and then finding a match in Source.

If there's no match, its blank and moves on to the next time slot.

If there is a match, the the result is the rad (random associated data) in Source C - I believe this will need to utilize the offset function as this is just an example sheet - the actual data im working with is likely to be in a different column.

I also appreciate the layout of Unfinished/Final Form might be considered a bit weird as the only header information is in A1:B2 and there are no headers at all in the Source data.

Also Final Form looks like an ugly, haphazard way of presenting the data, but for my purposes it makes a lot of sense once totally finished :)

I should also say that the Source data goes down to an unknown row (its variable) but the same part number should never occur more than once in the same time slot (if it does, the problem is more serious than just this workbook lol).

Any help on writing the code for this would be appreciated. I thought about creating a scripting dictionary which contains the source data but I'm not sure that will help... alternatively I know how I would do it with a formula using If, And, Match, and Offset - but that would result in a lot of formulas and just be completely horrible so its a non-starter.

Thanks for any help!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Luke777

Board Regular
Joined
Aug 10, 2020
Messages
213
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I'll present my three worksheets first and explain afterwards. I think showing the starting condition and the end condition makes it easier to show what I'm after.

Book2
ABCDEFG
1Part # >1010104325023984511289
2Time Slot
306/01/2022 06:3006/01/2022 06:45
406/01/2022 06:4506/01/2022 07:00
506/01/2022 07:0006/01/2022 07:15
606/01/2022 07:1506/01/2022 07:30
706/01/2022 07:3006/01/2022 07:45
8
907/01/2022 06:1507/01/2022 06:30
Unfinished


Book2
ABC
110101006/01/2022 06:30Rad
24325006/01/2022 06:30Chickens
32306/01/2022 06:3042
410101006/01/2022 07:00Batman
59845106/01/2022 07:00A Hat
6128906/01/2022 07:00Richard Hammond
7128906/01/2022 07:30Flamingo
8
92307/01/2022 06:15Sprouts
Source


Book2
ABCDEFG
1Part # >1010104325023984511289
2Time Slot
306/01/2022 06:3006/01/2022 06:45RadChickens42
406/01/2022 06:4506/01/2022 07:00
506/01/2022 07:0006/01/2022 07:15BatmanA HatRichard Hammond
606/01/2022 07:1506/01/2022 07:30
706/01/2022 07:3006/01/2022 07:45Flamingo
8
907/01/2022 06:1507/01/2022 06:30Sprouts
Final Form


The times in B of Unfinished/Final Form can be ignored as far as I understand.

To create the "Final Form" I am simply looking at the part # and Time Slot in A and then finding a match in Source.

If there's no match, its blank and moves on to the next time slot.

If there is a match, the the result is the rad (random associated data) in Source C - I believe this will need to utilize the offset function as this is just an example sheet - the actual data im working with is likely to be in a different column.

I also appreciate the layout of Unfinished/Final Form might be considered a bit weird as the only header information is in A1:B2 and there are no headers at all in the Source data.

Also Final Form looks like an ugly, haphazard way of presenting the data, but for my purposes it makes a lot of sense once totally finished :)

I should also say that the Source data goes down to an unknown row (its variable) but the same part number should never occur more than once in the same time slot (if it does, the problem is more serious than just this workbook lol).

Any help on writing the code for this would be appreciated. I thought about creating a scripting dictionary which contains the source data but I'm not sure that will help... alternatively I know how I would do it with a formula using If, And, Match, and Offset - but that would result in a lot of formulas and just be completely horrible so its a non-starter.

Thanks for any help!
I also should have said that "..." is just me being too lazy to give all the example data that could exist, so the data ending at Row 9 is for example purposes :)
 

Luke777

Board Regular
Joined
Aug 10, 2020
Messages
213
Office Version
  1. 365
Platform
  1. Windows
Anyone know the name of what sort of action I'm trying to do here?

It's like a look up or cross-reference or something - I'm not sure how to start researching what I'm trying to do
 

Forum statistics

Threads
1,176,276
Messages
5,902,295
Members
434,959
Latest member
etb1025

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top