Matching and align data within different rows

[paulahunter3]

Our clients submit forecast schedules to us weekly and every week we analyse the difference between what they asked for in the last schedule and what's required in the new. We call this a swing report.

The old and new schedules/orders change each week so demand for one date might be cancelled out for the second week but we don't record zero so we need to manually match up each part number with each date and so we can compare if the demand has went up or down since the last report.

I have attached a sample report and also included the finished end result so its easier to see what i'm looking for.

I have tried conditional formatting but as the rows don't match and are also missing information this doesn't really work. I can do it on a per part number basis but that only highlights the differences and then I still need to manually drop down a line and enter the new date. This took me 3 hours to do so any help would be much appreciated.

Thanks

[FlameRetired]

Hi paulahunter3 and welcome to the forum.

So I am clear.

Is the 'SwingData' sheet the source data and the 'Complete' sheet the desired output?

[paulahunter3]

Hi paulahunter3 and welcome to the forum.

So I am clear.

Is the 'SwingData' sheet the source data and the 'Complete' sheet the desired output?

Yes this is correct

[FlameRetired]

OK

I note there are only 3 rows of 7445xw in the 'SwingData' sheet yet you expect 4 rows in 'Complete' sheet.
Also 4 rows of 7445xx in the source but 6 rows in the summary.

I haven't checked the rest of Old/New Order book listings.

What am I missing?

[CAntosh]

Like FlameRetired, I'm not 100% sure I fully understand what you're looking for, but see if Sheet1 on the attachment will work. I merged the columns using the formula below in A2, filled down and filled right to cover columns A and B:

=IFERROR(INDEX(SwingData!$A$2:$C$534,ROWS(A$2:A2),COLUMN(A2)),IFERROR(INDEX(SwingData!$E$2:$G$597,ROWS(A$2:A2)-ROWS(SwingData!$A$2:$C$534),COLUMN(A2)),"-"))

I then split the formula, using the following in C2:

=IFERROR(INDEX(SwingData!$A$2:$C$534,ROWS(C$2:C2),COLUMN(C2)),0)

and D2:

=IFERROR(IF(C2>0,0,INDEX(SwingData!$E$2:$G$597,ROWS(C$2:C2)-ROWS(SwingData!$A$2:$C$534),COLUMN(C2))),0)

and E2 for Swing:

=D2-C2

Fill down. This creates an unsorted but merged data table. You can then use a standard pivot table to get the data sorted. The downside to my current approach is that your ranges must be precise in the formula, so you'd have to change them as you add data. That nuisance could be fixed by creating dynamic named ranges for each of your tables. Give the attachment a look, see if it'll do:

[paulahunter3]

I have missed a step out really which I should have included in my original post, apologies.

the completed sheet is the final result but a manual process, the step before this (completedsheet) would look like this.

if I take the first part no - 7445xw - the original data looks like this;

originalrawswing.png

there are 4 entries in the order new book but only 3 in the old so I manually match the dates up like this

onceihavematched.png

then I copy the dates from the missing cells and copy over so that for example 27/2 on the new order book would be filled in but with a 0 delivery remainder.

that's now what you see in the completed sheet

I have attached this as an example also as this table has messed up when copying.

[CAntosh]

Does the pivot table in post #5 work? It seems to give all of the data you're looking for without the redundancy of two separate tables. Or do you require the two-table layout from your "completed" sheet?

[paulahunter3]

Does the pivot table in post #5 work? It seems to give all of the data you're looking for without the redundancy of two separate tables. Or do you require the two-table layout from your "completed" sheet?

Hi CAntosh,

Apologies I never got a change to reply over the weekend.

Yes it works!! Thank you so much for this, this will save me so much time.

Thank you so much for your assistance

[paulahunter3]

Like FlameRetired, I'm not 100% sure I fully understand what you're looking for, but see if Sheet1 on the attachment will work. I merged the columns using the formula below in A2, filled down and filled right to cover columns A and B:

=IFERROR(INDEX(SwingData!$A$2:$C$534,ROWS(A$2:A2),COLUMN(A2)),IFERROR(INDEX(SwingData!$E$2:$G$597,ROWS(A$2:A2)-ROWS(SwingData!$A$2:$C$534),COLUMN(A2)),"-"))

I then split the formula, using the following in C2:

=IFERROR(INDEX(SwingData!$A$2:$C$534,ROWS(C$2:C2),COLUMN(C2)),0)

and D2:

=IFERROR(IF(C2>0,0,INDEX(SwingData!$E$2:$G$597,ROWS(C$2:C2)-ROWS(SwingData!$A$2:$C$534),COLUMN(C2))),0)

and E2 for Swing:

=D2-C2

Fill down. This creates an unsorted but merged data table. You can then use a standard pivot table to get the data sorted. The downside to my current approach is that your ranges must be precise in the formula, so you'd have to change them as you add data. That nuisance could be fixed by creating dynamic named ranges for each of your tables. Give the attachment a look, see if it'll do:

------------------------
Hi Cantosh,

The issue you mentioned above has came to light and I was wondering if you could increase the range for the spreadsheet for me? I tried to do it myself but didn't do to well.

I have added in a good few extra lines on top of our max of what I need to this should now work for all our clients going forward.

I have attached this spreadsheet for you. As you can see in the completed pivot table it only shows up to 10th April but the swing data goes up to 8th May.

Thanks

[CAntosh]

Try the attached version. I turned your old and new ranges on SwingData into dynamic named ranges called OldData and NewData, so the size of the named ranges will change automatically as you add/subtract rows on SwingData. The combined list on "Completed" will update automatically, as well. If you fill the formulas on "Completed" down much further than you need (I turned dashes into blanks for a cleaner look), you shouldn't need to adjust them ever again. You'll just need to make your changes to SwingData, then refresh the pivotTable.

OldData: =OFFSET(SwingData!$A$2,0,0,COUNTA(SwingData!$A:$A)-1,3)

NewData: =OFFSET(SwingData!$E$2,0,0,COUNTA(SwingData!$E:$E)-1,3)

The formulas on "completed" are structurally the same, but they use OldData and NewData to replace the respective ranges. For instance, the formula in A2 is now:

=IFERROR(INDEX(OldData,ROWS(A$2:A2),COLUMN(A2)),IFERROR(INDEX(NewData,ROWS(A$2:A2)-ROWS(OldData),COLUMN(A2)),""))

This should be easier to work with going forward.

[paulahunter3]

Hi Cantosh,

I tried using this sheet but when I enter new data in the swing sheet no results show on the completed page and the pivot table is blank.

Thanks
Paula

[CAntosh]

Does the pivot table show data normally, then GO blank when you add new data? For me, when I add test data to Row 838 of SwingData, that line automatically shows up on row 838 of Completed, and right-clicking the PivotTable and selecting 'Refresh' updates the PivotTable to include a new entry for the order book name 'test'.