Find matching criteria, add data from one sheet to another on matching row and Loop

sdennant

New Member
Joined
Mar 17, 2020
Messages
41
Office Version
  1. 365
Platform
  1. Windows
Hi all, I hope someone can help me regarding a macro to essentially remove some of the manual work this task requires. Here goes...

I have built this sheet called "Template". This sheet is the data recieved from the client.

1675806567181.png


And below "Tracker" is the sheet I input this data in, but sometimes the above sheet "Template" can be rather large.

1675806999269.png


What I would like to occur is this:
  • First to look for the first ISBN (9780000000001) from "Template" in "Tracker". If it does find it, check to see if its criteria matches that of the "Template" i.e. If the Terms % on (Template column F) match the Terms % on (Tracker Column J) as well as if the Claim Start/Claim End match or is within the Deal Start/Deal End dates. As some lines with the same ISBN could have different Deal Start and End Dates.
  • The next step would be then to input the QTY from "Template" into the matching line in "Tracker" Column Q (Claim QTY). If however, the QTY is higher than the Estimate QTY in Column O then to instead add it to the Unplanned Claim QTY in Column S.
  • Then to Add the Claim Start/Claim End from "Template" into the Claim Start Date/Claim End Date (Columns U/T in "Tracker" and also the Customer Reference Column K into Column AA.
  • And Loop for each ISBN from "Template".
The only other thing, which is fiddly would be to:
  • If for instance, any of the ISBNS/Terms % or Claim Start/End Date from "Template" do not match any row from "Tracker" to flag up as a message box stating that these lines werent in the grid and have been added to the bottom of the "Tracker" copying over the missing ISBN, Claim Start/End Date and Term Percentage and Customer Reference from "Template" into the "Tracker" at the next available row/row 14 in this example.
I appreciate this might be impossible or a tall ask, but any help would be welcome.

All the best,
S
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Will do. I couldn’t when writing this out as my work laptop doesn’t allow me to install anything. I’ll do this shortly on my personal one. Hang tight.
 
Upvote 0
Hi again, so below is the XL2BB format:

Sheet called "Template":

Retro Macro.xlsm
ABCDEFGHIJK
2ISBNTitlePublisherRRPQTYTermsClaim StartClaim EndMonthYearReference
39780000000001Book 1OPG£20.00148%25/12/202231/12/2022DEC2264047 OPG 080223
49780000000002Book 2H&S£30.0098%25/12/202231/12/2022DEC2264047 H&S 080223
59780000000003Book 3LBBG£16.99148%25/12/202231/12/2022DEC2264047 LBBG 080223
69780000000004Book 4OPG£26.003458%25/12/202231/12/2022DEC2264047 OPG 080223
79780000000005Book 5H&S£12.99178%25/12/202231/12/2022DEC2264047 H&S 080223
89780000000013Book 13LBBG£16.991168%25/12/202231/12/2022DEC2264047 LBBG 080223
Template
Cell Formulas
RangeFormula
I3:I8I3=UPPER(TEXT(G3,"MMM"))
J3:J8J3=TEXT(G3, "YY")
K3:K8K3=IF($M$2<"",$M$2&" "& C3 &" "&TEXT(TODAY(),"ddmmyy"),$N$2&" "&I3&J3&" ")


And here's the sheet called "Tracker":

Retro Macro.xlsm
DEFGHIJKLMNOPQRSTUVWXYZAA
1ISBNTitleDivisionRRPPub DateRetro DescriptionRetro Terms %Retro terms (£ per copy)MonthDeal Start dateDeal End DateEstimate QTYEstimate ValueClaim QTYClaim ValueUnplanned Claim QTYUnplanned Claim ValueClaim Start dateClaim End DateDiscount Credit at Recharge at Date Claim receivedCustomer Claim Reference number
29780000000001Book 1OPG£ 20.0007/02/2023Jan Sale 20238.00%£ 1.60Dec25/12/202231/12/2022300£ 480.00£ -£ -30%£ 14.00£ 12.40
39780000000002Book 2H&S£ 30.0007/02/2023Jan Sale 20238.00%£ 2.40Dec25/12/202231/12/2022300£ 720.00£ -£ -30%£ 21.00£ 18.60
49780000000003Book 3LBBG£ 16.9907/02/2023Jan Sale 20238.00%£ 1.36Dec25/12/202231/12/2022300£ 407.76£ -£ -30%£ 11.89£ 10.53
59780000000004Book 4OPG£ 26.0007/02/2023Jan Sale 20238.00%£ 2.08Dec25/12/202231/12/2022300£ 624.00£ -£ -30%£ 18.20£ 16.12
69780000000005Book 5H&S£ 12.9907/02/2023Jan Sale 20238.00%£ 1.04Dec25/12/202231/12/2022300£ 311.76£ -£ -30%£ 9.09£ 8.05
79780000000006Book 6LBBG£ 14.9907/02/2023Jan Sale 20238.00%£ 1.20Dec25/12/202231/12/2022300£ 359.76£ -£ -30%£ 10.49£ 9.29
89780000000007Book 7LBBG£ 16.9907/02/2023Jan Sale 20238.00%£ 1.36Dec11/12/202217/12/2022300£ 407.76100£ 135.92£ -30%£ 11.89£ 10.5364047 LBBG 080223
99780000000007Book 7LBBG£ 16.9907/02/2023Jan Sale 20238.00%£ 1.36Dec18/12/202214/12/2022200£ 271.84150£ 203.88£ -30%£ 11.89£ 10.5364047 LBBG 080223
109780000000008Book 9LBBG£ 9.9907/02/2023Jan Sale 20238.00%£ 0.80Dec25/12/202231/12/2022175£ 139.86£ -£ -30%£ 6.99£ 6.19
119780000000009Book 10OPG£ 45.0007/02/2023Jan Sale 20238.00%£ 3.60Dec25/12/202231/12/2022175£ 630.00£ -£ -30%£ 31.50£ 27.90
129780000000010Book 11H&S£ 2.0007/02/2023Jan Sale 20238.00%£ 0.16Dec25/12/202231/12/202280£ 12.80£ -£ -30%£ 1.40£ 1.24
139780000000007Book 7LBBG£ 16.9907/02/2023Jan Sale 20238.00%£ 1.36Dec25/12/202231/12/202250£ 67.96£ -£ -30%£ 11.89£ 10.53
Tracker


Best,
S
 
Upvote 0
Hi S,

I might have sorted out the answer for the first 4 points you have raised. Just formulas, no macros.
If that fits (You'll need to check out not only my additions but also the old formulas in both Tracker and Template) let me know and we could add dynamic formulas and a paste down macro so that you do not need to adjust the size.

ISBN.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
112346978Audit Data 557811
2ISBNTitleDivisionRRPPub DateRetro DescriptionRetro Terms %Retro terms (£ per copy)MonthDeal Start dateDeal End DateEstimate QTYEstimate ValueISBN CheckTerms CheckStart Date = or WithinEnd Date CheckClaim QTYClaim ValueUnplanned Claim QTYUnplanned Claim ValueClaim Start dateClaim End DateDiscount Credit at Recharge at Date Claim receivedCustomer Claim Reference number
39780000000001Book 1OPG2044964Jan Sale 20238%1.6Dec25-Dec-2231-Dec-22300480TRUE0.08TRUETRUE1422.40025-Dec-2231-Dec-220.31412.4OPG 090223
49780000000002Book 2H&S3044964Jan Sale 20238%2.4Dec25-Dec-2231-Dec-22300720TRUE0.08TRUETRUE921.60025-Dec-2231-Dec-220.32118.6H&S 090223
59780000000003Book 3LBBG16.9944964Jan Sale 20238%1.3592Dec25-Dec-2231-Dec-22300407.76TRUE0.08TRUETRUE1419.02880025-Dec-2231-Dec-220.311.89310.5338LBBG 090223
69780000000004Book 4OPG2644964Jan Sale 20238%2.08Dec25-Dec-2231-Dec-22300624TRUE0.08TRUETRUEUps! 345717.625-Dec-2231-Dec-220.318.216.12OPG 090223
79780000000005Book 5H&S12.9944964Jan Sale 20238%1.0392Dec25-Dec-2231-Dec-22300311.76TRUE0.08TRUETRUE1717.66640025-Dec-2231-Dec-220.39.0938.0538H&S 090223
89780000000006Book 6LBBG14.9944964Jan Sale 20238%1.1992Dec25-Dec-2231-Dec-22300359.76#N/A#N/A#N/A#N/A#N/A #N/A #N/A#N/A0.310.4939.2938#N/A
99780000000007Book 7LBBG16.9944964Jan Sale 20238%1.3592Dec11-Dec-2217-Dec-22300407.76TRUE0.08TRUETRUE100135.920011-Dec-2217-Dec-220.311.89310.5338LBBG 090223
109780000000007Book 7LBBG16.9944964Jan Sale 20238%1.3592Dec18-Dec-2214-Dec-22200271.84TRUE0.08FALSEFALSE000011-Dec-2217-Dec-220.311.89310.5338LBBG 090223
119780000000008Book 9LBBG9.9944964Jan Sale 20238%0.7992Dec25-Dec-2231-Dec-22175139.86TRUE0.08FALSEFALSE000018-Dec-2214-Dec-220.36.9936.1938LBBG 090223
129780000000009Book 10OPG4544964Jan Sale 20238%3.6Dec25-Dec-2231-Dec-22175630#N/A#N/A#N/A#N/A#N/A #N/A #N/A#N/A0.331.527.9#N/A
139780000000010Book 11H&S244964Jan Sale 20238%0.16Dec25-Dec-2231-Dec-228012.8#N/A#N/A#N/A#N/A#N/A #N/A #N/A#N/A0.31.41.24#N/A
149780000000007Book 7LBBG16.9944964Jan Sale 20238%1.3592Dec25-Dec-2231-Dec-225067.96TRUE0.08FALSEFALSE000011-Dec-2217-Dec-220.311.89310.5338LBBG 090223
15
16ABCDEFGHIJK
1711234567891011
182ISBNTitlePublisherRRPQTYTermsClaim StartClaim EndMonthYearReference
1939780000000001Book 1OPG20148%25-Dec-2231-Dec-22DEC22OPG 090223
2049780000000002Book 2H&S3098%25-Dec-2231-Dec-22DEC22H&S 090223
2159780000000003Book 3LBBG16.99148%25-Dec-2231-Dec-22DEC22LBBG 090223
2269780000000004Book 4OPG263458%25-Dec-2231-Dec-22DEC22OPG 090223
2379780000000005Book 5H&S12.99178%25-Dec-2231-Dec-22DEC22H&S 090223
2489780000000013Book 13LBBG16.991168%25-Dec-2231-Dec-22DEC22LBBG 090223
2599780000000007Book 7LBBG16.991008%11-Dec-2217-Dec-22Dec22LBBG 090223
26109780000000008Book 9LBBG9.991508%18-Dec-2214-Dec-22Dec22LBBG 090223
Tracker
Cell Formulas
RangeFormula
K3:K14K3=IFERROR((G3*J3),"")
L3:L14L3=(M3-DAY(M3)+1)
P3:P14P3=IFERROR((O3*K3),"")
Q3:Q14Q3=INDEX(Template!$A$3:$K$10,MATCH(Tracker!$D3,Template!$A$3:$A$10,0),MATCH(Tracker!D$1,Template!$A$1:$K$1,0))=D3
R3:R14R3=INDEX(Template!$A$3:$K$10,MATCH(D3,Template!$A$3:$A$10,0),MATCH(J$1,Template!$A$1:$K$1,0))
S3:S14S3=AND(INDEX(Template!$A$3:$K$10,MATCH($D3,Template!$A$3:$A$10,0),MATCH(M$1,Template!$A$1:$K$1,0))>=M3,INDEX(Template!$A$3:$K$10,MATCH($D3,Template!$A$3:$A$10,0),MATCH(M$1,Template!$A$1:$K$1,0))<N3)
T3:T14T3=INDEX(Template!$A$3:$K$10,MATCH($D3,Template!$A$3:$A$10,0),MATCH(N$1,Template!$A$1:$K$1,0))=N3
U3:U14U3=IF(INDEX(Template!$A$3:$K$10,MATCH(Tracker!$D3,Template!$A$3:$A$10,0),MATCH(Tracker!U$1,Template!$A$1:$K$1,0))*$S3*$T3>$O3,"Ups!",INDEX(Template!$A$3:$K$10,MATCH(Tracker!$D3,Template!$A$3:$A$10,0),MATCH(Tracker!U$1,Template!$A$1:$K$1,0))*$S3*$T3)
V3:V14V3=IFERROR((U3*K3),"")
W3:W14W3=IF(INDEX(Template!$A$3:$K$10,MATCH(Tracker!$D3,Template!$A$3:$A$10,0),MATCH(Tracker!W$1,Template!$A$1:$K$1,0))*$S3*$T3<$O3,0,INDEX(Template!$A$3:$K$10,MATCH(Tracker!$D3,Template!$A$3:$A$10,0),MATCH(Tracker!W$1,Template!$A$1:$K$1,0))*$S3*$T3)
X3:X14X3=IFERROR((W3*K3),"")
AE3:AE14,Y3:Z14Y3=INDEX(Template!$A$3:$K$10,MATCH(Tracker!$D3,Template!$A$3:$A$10,0),MATCH(Tracker!Y$1,Template!$A$1:$K$1,0))
AB3:AB14AB3=IFERROR((G3*(1-AA3)),"")
AC3:AC14AC3=IFERROR((AB3-K3),"")
D19:N24D19=Template!A3
L25:L26L25=L9
N25:N26N25=F25&" "&TEXT(TODAY(),"ddmmyy")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AE3:AE14Expression=ISERROR(AE3)textNO
Y3:Z14Expression=ISERROR(Y3)textNO
D17:N17Expression=ISNUMBER(D17)textNO
D1:AE1Expression=ISNUMBER(D1)textNO
W3:W14Expression=W3="Ups!"textNO
W3:W14Expression=ISERROR(W3)textNO
U3:U14Expression=U3="Ups!"textNO
U3:U14Expression=ISERROR(U3)textNO
M3:M14Expression=M3>N3textNO
N3:N14Expression=N3<M3textNO
T3:T14Expression=T3=FALSEtextNO
T3:T14Expression=ISERROR(T3)textNO
S3:S14Expression=S3=FALSEtextNO
S3:S14Expression=ISERROR(S3)textNO
R3:R14Expression=ISERROR(R3)textNO
Q3:Q14Expression=ISERROR(Q3)textNO
bc:FCE4D6]text[/XD][XD]NO[/XD][/XR][XR][XD]N3:N14[/XD][XD]Expression[/XD][XD]=N3<M3[/XD][XD=bc:FCE4D6]text[/XD][XD]NO[/XD][/XR][/RANGE]

ISBN.xlsm
ABCDEFGHIJK
11234567891011
2ISBNTitlePublisherRRPQTYTermsClaim StartClaim EndMonthYearReference
39.78E+12Book 1OPG20140.084492044926DEC22OPG 090223
49.78E+12Book 2H&S3090.084492044926DEC22H&S 090223
59.78E+12Book 3LBBG16.99140.084492044926DEC22LBBG 090223
69.78E+12Book 4OPG263450.084492044926DEC22OPG 090223
79.78E+12Book 5H&S12.99170.084492044926DEC22H&S 090223
89.78E+12Book 13LBBG16.991160.084492044926DEC22LBBG 090223
99780000000007Book 7LBBG171008%4490644912Dec22LBBG 090223
109780000000008Book 9LBBG101508%4491344909Dec22LBBG 090223
Template
Cell Formulas
RangeFormula
I3:I8I3=UPPER(TEXT(G3,"MMM"))
J3:J8J3=TEXT(G3, "YY")
K3:K10K3=C3&" "&TEXT(TODAY(),"ddmmyy")
A9:J10A9=Tracker!D25



File for download here, too: ISBN.xlsm


For the last point I am not sure I get it. Wondering if a filter and cond. format could sort it out.

Cheers!,

G
 
Last edited:
Upvote 0
Thanks, that's a remarkabe way to have worked this out. Let me check this and get right back to you and i'll need to maybe revise the last point.

S
 
Upvote 0
If you have office 365 you may use xlookup instead of Index+Match
 
Upvote 0
So i've had a look through, and not gonna lie this is a beautiful way of solving this with formulas. It could potentially work for smaller sheets.

The only thing is, the “tracker” sheet can be 50k lines for a year and formulas after a while will be quite taxing on the sheet. And It’s already filled with all kinds of formulas already. I’d probably after every time New Template data comes in need to make sure that I’ve copied and pasted the added QTY, start and end dates and any other information over itself to keep it where it is for the next time so it won’t be removed when new template information comes in.

Hope that makes sense?

Could this become a macro? As I think maybe macros have more of a concrete way of doing this.

The last point in a nutshell is one more check: a sort of If Else statement:

  • If for instance, any of the ISBNS/Terms % or Claim Start/End Date from "Template" do not match any row from "Tracker" or already has a Claim QTY (That means that that line from the “Template” isn’t in the sheet at all) and is a new edition.
  • For it to flag up as a message box stating that these lines weren’t in the grid and have been added to the bottom of the "Tracker" copying over the missing ISBN and Customer Reference from "Template" into the "Tracker" at the next available row/row 14 in this example but that depends how long the sheet ends up.
Again though thanks for the above work, it’s a pretty good starting point.

Best,
S
 
Upvote 0
Having said that though, if a macro wouldn’t be better or possible then I could potentially make this work. It’s really to streamline this process as much as possible factoring in all kinds of scenarios so it’s always correct as a lot of people work in this sheet who will accidentally fiddle with the formulas and inadvertently make them look elsewhere.
 
Upvote 0
Hi S,

50k - with a paste down macro you have just one row for all of the formulas (which are equivalent by column). It works like this:

1 - Update the tables with the new entry data
2 - Copy the row of formulas
3 - Paste it down to do all calculations (here you need dynamic formulas which are able to figure how many rows down to paste)
4 - Copy the newly performed calculations
5 - Paste as values

It is a great technique, you can learn it here: Fast Excel Development Method e-Learning. I am not related in any way with PS, simply impressed with their work, helps me quite.

Add New Templates - I think this is doable as well with Paste Down Macros. That may require another worksheet to add up new data.
An even better solution could be to use Power Query which can easily automate appending the new data.

Does the New Template data come in as:
1 - a data range (table) in the existing file?
2 - as a file?
3- as a folder? (well, a new file in a new folder...)


Does the Tracker data range/Table add up all of the new templates, as they come?
Could the templates contain duplicate values?
Can the Tracker contain duplicates?


Other people - protection helps, if suitable. They should not be able to touch the formulas just specific cells (formatted differently?).

All macros solution - it is certainly possible but not an expert myself (it would take me weeks or months to figure it all out). I think you might need a little application to handle all that.

All Power Query Solution - it could be possible as well. PQ performs lookups seamlessly with no programming required (M language). Beyond that I am not sure if everything else can be solved in PQ without M.

  • If for instance, any of the ISBNS/Terms % or Claim Start/End Date from "Template" do not match any row from "Tracker"
    • in the solution above this is dealt with - If there is no match you already have a #NA which could be filtered at the bottom
    • A message box can count how many rows are #N/A at the bottom
  • or already has a Claim QTY (That means that that line from the “Template” isn’t in the sheet at all) and is a new edition.
    • I still do not picture this. Maybe an example could help

  • For it to flag up as a message box stating that these lines weren’t in the grid and have been added to the bottom of the "Tracker"
    • A message box can count how many rows are #N/A at the bottom
  • copying over the missing ISBN and Customer Reference from "Template" into the "Tracker" at the next available row/row 14 in this example but that depends how long the sheet ends up.
    • in the example Book 6, 10 & 11 are in the Tracker but not in the Template. It is only Book 13 which is in Temp and not in Tracker. The row of Book 13 should be pasted as row 15 in Tracker?
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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
Back
Top