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

[sdennant]

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.

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

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

 

[gelu]

Hi,

Please use XL2BB - Excel Range to BBCode to post values.
Otherwise people need to type your data in order to help and that is a bother, prevents you from getting help.

G

 

[sdennant]

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.

 

[sdennant]

Hi again, so below is the XL2BB format:

Sheet called "Template":

Retro Macro.xlsm
	A	B	C	D	E	F	G	H	I	J	K
2	ISBN	Title	Publisher	RRP	QTY	Terms	Claim Start	Claim End	Month	Year	Reference
3	9780000000001	Book 1	OPG	£20.00	14	8%	25/12/2022	31/12/2022	DEC	22	64047 OPG 080223
4	9780000000002	Book 2	H&S	£30.00	9	8%	25/12/2022	31/12/2022	DEC	22	64047 H&S 080223
5	9780000000003	Book 3	LBBG	£16.99	14	8%	25/12/2022	31/12/2022	DEC	22	64047 LBBG 080223
6	9780000000004	Book 4	OPG	£26.00	345	8%	25/12/2022	31/12/2022	DEC	22	64047 OPG 080223
7	9780000000005	Book 5	H&S	£12.99	17	8%	25/12/2022	31/12/2022	DEC	22	64047 H&S 080223
8	9780000000013	Book 13	LBBG	£16.99	116	8%	25/12/2022	31/12/2022	DEC	22	64047 LBBG 080223
Template

Cell Formulas
Range	Formula
I3:I8	I3	=UPPER(TEXT(G3,"MMM"))
J3:J8	J3	=TEXT(G3, "YY")
K3:K8	K3	=IF($M$2<"",$M$2&" "& C3 &" "&TEXT(TODAY(),"ddmmyy"),$N$2&" "&I3&J3&" ")

And here's the sheet called "Tracker":

Retro Macro.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1	ISBN	Title	Division	RRP	Pub Date	Retro Description	Retro Terms %	Retro terms (£ per copy)	Month	Deal Start date	Deal End Date	Estimate QTY	Estimate Value	Claim QTY	Claim Value	Unplanned Claim QTY	Unplanned Claim Value	Claim Start date	Claim End Date	Discount	Credit at	Recharge at	Date Claim received	Customer Claim Reference number
2	9780000000001	Book 1	OPG	£ 20.00	07/02/2023	Jan Sale 2023	8.00%	£ 1.60	Dec	25/12/2022	31/12/2022	300	£ 480.00		£ -		£ -			30%	£ 14.00	£ 12.40
3	9780000000002	Book 2	H&S	£ 30.00	07/02/2023	Jan Sale 2023	8.00%	£ 2.40	Dec	25/12/2022	31/12/2022	300	£ 720.00		£ -		£ -			30%	£ 21.00	£ 18.60
4	9780000000003	Book 3	LBBG	£ 16.99	07/02/2023	Jan Sale 2023	8.00%	£ 1.36	Dec	25/12/2022	31/12/2022	300	£ 407.76		£ -		£ -			30%	£ 11.89	£ 10.53
5	9780000000004	Book 4	OPG	£ 26.00	07/02/2023	Jan Sale 2023	8.00%	£ 2.08	Dec	25/12/2022	31/12/2022	300	£ 624.00		£ -		£ -			30%	£ 18.20	£ 16.12
6	9780000000005	Book 5	H&S	£ 12.99	07/02/2023	Jan Sale 2023	8.00%	£ 1.04	Dec	25/12/2022	31/12/2022	300	£ 311.76		£ -		£ -			30%	£ 9.09	£ 8.05
7	9780000000006	Book 6	LBBG	£ 14.99	07/02/2023	Jan Sale 2023	8.00%	£ 1.20	Dec	25/12/2022	31/12/2022	300	£ 359.76		£ -		£ -			30%	£ 10.49	£ 9.29
8	9780000000007	Book 7	LBBG	£ 16.99	07/02/2023	Jan Sale 2023	8.00%	£ 1.36	Dec	11/12/2022	17/12/2022	300	£ 407.76	100	£ 135.92		£ -			30%	£ 11.89	£ 10.53		64047 LBBG 080223
9	9780000000007	Book 7	LBBG	£ 16.99	07/02/2023	Jan Sale 2023	8.00%	£ 1.36	Dec	18/12/2022	14/12/2022	200	£ 271.84	150	£ 203.88		£ -			30%	£ 11.89	£ 10.53		64047 LBBG 080223
10	9780000000008	Book 9	LBBG	£ 9.99	07/02/2023	Jan Sale 2023	8.00%	£ 0.80	Dec	25/12/2022	31/12/2022	175	£ 139.86		£ -		£ -			30%	£ 6.99	£ 6.19
11	9780000000009	Book 10	OPG	£ 45.00	07/02/2023	Jan Sale 2023	8.00%	£ 3.60	Dec	25/12/2022	31/12/2022	175	£ 630.00		£ -		£ -			30%	£ 31.50	£ 27.90
12	9780000000010	Book 11	H&S	£ 2.00	07/02/2023	Jan Sale 2023	8.00%	£ 0.16	Dec	25/12/2022	31/12/2022	80	£ 12.80		£ -		£ -			30%	£ 1.40	£ 1.24
13	9780000000007	Book 7	LBBG	£ 16.99	07/02/2023	Jan Sale 2023	8.00%	£ 1.36	Dec	25/12/2022	31/12/2022	50	£ 67.96		£ -		£ -			30%	£ 11.89	£ 10.53
Tracker

Best,
S

 

[gelu]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1				1	2	3	4			6		9	7	8			Audit Data				5		5		7	8					11
2				ISBN	Title	Division	RRP	Pub Date	Retro Description	Retro Terms %	Retro terms (£ per copy)	Month	Deal Start date	Deal End Date	Estimate QTY	Estimate Value	ISBN Check	Terms Check	Start Date = or Within	End Date Check	Claim QTY	Claim Value	Unplanned Claim QTY	Unplanned Claim Value	Claim Start date	Claim End Date	Discount	Credit at	Recharge at	Date Claim received	Customer Claim Reference number
3				9780000000001	Book 1	OPG	20	44964	Jan Sale 2023	8%	1.6	Dec	25-Dec-22	31-Dec-22	300	480	TRUE	0.08	TRUE	TRUE	14	22.4	0	0	25-Dec-22	31-Dec-22	0.3	14	12.4		OPG 090223
4				9780000000002	Book 2	H&S	30	44964	Jan Sale 2023	8%	2.4	Dec	25-Dec-22	31-Dec-22	300	720	TRUE	0.08	TRUE	TRUE	9	21.6	0	0	25-Dec-22	31-Dec-22	0.3	21	18.6		H&S 090223
5				9780000000003	Book 3	LBBG	16.99	44964	Jan Sale 2023	8%	1.3592	Dec	25-Dec-22	31-Dec-22	300	407.76	TRUE	0.08	TRUE	TRUE	14	19.0288	0	0	25-Dec-22	31-Dec-22	0.3	11.893	10.5338		LBBG 090223
6				9780000000004	Book 4	OPG	26	44964	Jan Sale 2023	8%	2.08	Dec	25-Dec-22	31-Dec-22	300	624	TRUE	0.08	TRUE	TRUE	Ups!		345	717.6	25-Dec-22	31-Dec-22	0.3	18.2	16.12		OPG 090223
7				9780000000005	Book 5	H&S	12.99	44964	Jan Sale 2023	8%	1.0392	Dec	25-Dec-22	31-Dec-22	300	311.76	TRUE	0.08	TRUE	TRUE	17	17.6664	0	0	25-Dec-22	31-Dec-22	0.3	9.093	8.0538		H&S 090223
8				9780000000006	Book 6	LBBG	14.99	44964	Jan Sale 2023	8%	1.1992	Dec	25-Dec-22	31-Dec-22	300	359.76	#N/A	#N/A	#N/A	#N/A	#N/A		#N/A		#N/A	#N/A	0.3	10.493	9.2938		#N/A
9				9780000000007	Book 7	LBBG	16.99	44964	Jan Sale 2023	8%	1.3592	Dec	11-Dec-22	17-Dec-22	300	407.76	TRUE	0.08	TRUE	TRUE	100	135.92	0	0	11-Dec-22	17-Dec-22	0.3	11.893	10.5338		LBBG 090223
10				9780000000007	Book 7	LBBG	16.99	44964	Jan Sale 2023	8%	1.3592	Dec	18-Dec-22	14-Dec-22	200	271.84	TRUE	0.08	FALSE	FALSE	0	0	0	0	11-Dec-22	17-Dec-22	0.3	11.893	10.5338		LBBG 090223
11				9780000000008	Book 9	LBBG	9.99	44964	Jan Sale 2023	8%	0.7992	Dec	25-Dec-22	31-Dec-22	175	139.86	TRUE	0.08	FALSE	FALSE	0	0	0	0	18-Dec-22	14-Dec-22	0.3	6.993	6.1938		LBBG 090223
12				9780000000009	Book 10	OPG	45	44964	Jan Sale 2023	8%	3.6	Dec	25-Dec-22	31-Dec-22	175	630	#N/A	#N/A	#N/A	#N/A	#N/A		#N/A		#N/A	#N/A	0.3	31.5	27.9		#N/A
13				9780000000010	Book 11	H&S	2	44964	Jan Sale 2023	8%	0.16	Dec	25-Dec-22	31-Dec-22	80	12.8	#N/A	#N/A	#N/A	#N/A	#N/A		#N/A		#N/A	#N/A	0.3	1.4	1.24		#N/A
14				9780000000007	Book 7	LBBG	16.99	44964	Jan Sale 2023	8%	1.3592	Dec	25-Dec-22	31-Dec-22	50	67.96	TRUE	0.08	FALSE	FALSE	0	0	0	0	11-Dec-22	17-Dec-22	0.3	11.893	10.5338		LBBG 090223
15
16				A	B	C	D	E	F	G	H	I	J	K
17			1	1	2	3	4	5	6	7	8	9	10	11
18			2	ISBN	Title	Publisher	RRP	QTY	Terms	Claim Start	Claim End	Month	Year	Reference
19			3	9780000000001	Book 1	OPG	20	14	8%	25-Dec-22	31-Dec-22	DEC	22	OPG 090223
20			4	9780000000002	Book 2	H&S	30	9	8%	25-Dec-22	31-Dec-22	DEC	22	H&S 090223
21			5	9780000000003	Book 3	LBBG	16.99	14	8%	25-Dec-22	31-Dec-22	DEC	22	LBBG 090223
22			6	9780000000004	Book 4	OPG	26	345	8%	25-Dec-22	31-Dec-22	DEC	22	OPG 090223
23			7	9780000000005	Book 5	H&S	12.99	17	8%	25-Dec-22	31-Dec-22	DEC	22	H&S 090223
24			8	9780000000013	Book 13	LBBG	16.99	116	8%	25-Dec-22	31-Dec-22	DEC	22	LBBG 090223
25			9	9780000000007	Book 7	LBBG	16.99	100	8%	11-Dec-22	17-Dec-22	Dec	22	LBBG 090223
26			10	9780000000008	Book 9	LBBG	9.99	150	8%	18-Dec-22	14-Dec-22	Dec	22	LBBG 090223
Tracker

Cell Formulas
Range	Formula
K3:K14	K3	=IFERROR((G3*J3),"")
L3:L14	L3	=(M3-DAY(M3)+1)
P3:P14	P3	=IFERROR((O3*K3),"")
Q3:Q14	Q3	=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:R14	R3	=INDEX(Template!$A$3:$K$10,MATCH(D3,Template!$A$3:$A$10,0),MATCH(J$1,Template!$A$1:$K$1,0))
S3:S14	S3	=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:T14	T3	=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:U14	U3	=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:V14	V3	=IFERROR((U3*K3),"")
W3:W14	W3	=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:X14	X3	=IFERROR((W3*K3),"")
AE3:AE14,Y3:Z14	Y3	=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:AB14	AB3	=IFERROR((G3*(1-AA3)),"")
AC3:AC14	AC3	=IFERROR((AB3-K3),"")
D19:N24	D19	=Template!A3
L25:L26	L25	=L9
N25:N26	N25	=F25&" "&TEXT(TODAY(),"ddmmyy")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AE3:AE14	Expression	=ISERROR(AE3)	text	NO
Y3:Z14	Expression	=ISERROR(Y3)	text	NO
D17:N17	Expression	=ISNUMBER(D17)	text	NO
D1:AE1	Expression	=ISNUMBER(D1)	text	NO
W3:W14	Expression	=W3="Ups!"	text	NO
W3:W14	Expression	=ISERROR(W3)	text	NO
U3:U14	Expression	=U3="Ups!"	text	NO
U3:U14	Expression	=ISERROR(U3)	text	NO
M3:M14	Expression	=M3>N3	text	NO
N3:N14	Expression	=N3<M3	text	NO
T3:T14	Expression	=T3=FALSE	text	NO
T3:T14	Expression	=ISERROR(T3)	text	NO
S3:S14	Expression	=S3=FALSE	text	NO
S3:S14	Expression	=ISERROR(S3)	text	NO
R3:R14	Expression	=ISERROR(R3)	text	NO
Q3:Q14	Expression	=ISERROR(Q3)	text	NO

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
	A	B	C	D	E	F	G	H	I	J	K
1	1	2	3	4	5	6	7	8	9	10	11
2	ISBN	Title	Publisher	RRP	QTY	Terms	Claim Start	Claim End	Month	Year	Reference
3	9.78E+12	Book 1	OPG	20	14	0.08	44920	44926	DEC	22	OPG 090223
4	9.78E+12	Book 2	H&S	30	9	0.08	44920	44926	DEC	22	H&S 090223
5	9.78E+12	Book 3	LBBG	16.99	14	0.08	44920	44926	DEC	22	LBBG 090223
6	9.78E+12	Book 4	OPG	26	345	0.08	44920	44926	DEC	22	OPG 090223
7	9.78E+12	Book 5	H&S	12.99	17	0.08	44920	44926	DEC	22	H&S 090223
8	9.78E+12	Book 13	LBBG	16.99	116	0.08	44920	44926	DEC	22	LBBG 090223
9	9780000000007	Book 7	LBBG	17	100	8%	44906	44912	Dec	22	LBBG 090223
10	9780000000008	Book 9	LBBG	10	150	8%	44913	44909	Dec	22	LBBG 090223
Template

Cell Formulas
Range	Formula
I3:I8	I3	=UPPER(TEXT(G3,"MMM"))
J3:J8	J3	=TEXT(G3, "YY")
K3:K10	K3	=C3&" "&TEXT(TODAY(),"ddmmyy")
A9:J10	A9	=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

 

[sdennant]

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

 

[gelu]

If you have office 365 you may use xlookup instead of Index+Match

 

[sdennant]

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

 

[sdennant]

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.

 

[gelu]

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?