bit of a curly one!

[hepkat63]

Hi all,
I have a bit of a curly problem that I'm hoping someone can help me work though!
Every day, I am required to manipulate a couple of existing spreadsheets in able to consolidate data in to a certain format as so I can then copy/paste that information into SAP.
I can do this with lots of manual steps, however of course would like to automate it, but the variables have made it a confusing problem.
I have one spreadsheet called 'issues. This contains multiple columns which are populated as goods are issued from our store.
I have another spreadsheet that is created daily by downloading information from SAP. The name of this spreadsheet changes everyday with the download based on date.
First problem: How to isolate the required five columns and varying amount of rows of data from the 'issues' sheet that are between yesterdays date and todays date AND from the 2nd last row that has been highlighted in Yellow colour to the last row that is highlighted in the Yellow color (I highlight the row at the end of the shift to differentiate between shifts). Then select and copy this data to another spreadsheet
Second problem: In the downloaded spreadsheet from SAP, go to the first empty row (as each day the amount of data varies), then go to column number four and paste the data from Problem 1 (issues sheet)
Hoping someone can help!

 

[Colo]

Hi,
It would be easier to get answers if you posted the worksheets as a sample.

Have a look at here how to use XL2BB

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[hepkat63]

GoodsIssuedToSap_20220823_130304.xlsx
	A	B	C	D	E	F	G	H	I
1	MaintenanceTransactionNumber	Location	Description	SAPMaterialNumber	Quantity	UnE	SLoc	CostCentre	UserViewShort
2	123400028079	R1-7	HOLDER KNIFE SUPERGRINDER B371	800-802165	1		MS01	10801234	DBARNES
3	123400028091		PLUG S/S 3/8 INCH	800-756722	1		MS01	13161234	WRAMPONI
4	123400028092	B1-3	VALVE MFH-3-1/8 WAFMFH-3-18	800-786616	1		MS01	10201234	SJONES
5	123400028086		WIRE BRUSH S/S 4 ROW PLASTIC HANDLE	800-765716	1		MS01	10201234	SCOTT
6	123400028087	PLASTICS	BAG BARRIER BXGUARD 400X500 AI517 TPD	600-150374	2100		SL02	11901234	SROLFE
7	123400028087	PLASTICS	BAG BARRIER NEWTEQ 350 X 550 AI 517	600-161832	9000		SL02	11901234	SROLFE
8	123400028087	PLASTICS	BAG BARRIER NEWTEQ 225 X 790 AI 517	600-152055	3600		SL02	11901234	SROLFE
9	123400028078	PLASTICS	BAG BARRIER NEWTEQ 250 X 550 AI 517	600-161831	700		SL02	10201234	SROLFE
10	123400028087	PLASTICS	BAG BARRIER NEWTEQ 300 X 600 AI 517	600-150271	10000		SL02	11901234	SROLFE
11	123400028089	PLASTICS	BAG BARRIER NEWTEQ 325X700 AI517 TPD	600-176372	900		SL02	10201234	SROLFE
12	123400028087	PLASTICS	BAG BARRIER NEWTEQ 400X600USA AI517 TPD	600-150275	8000		SL02	11901234	SROLFE
13	123400028087	PLASTICS	BAG BARRIER NEWTEQ 425X750 USA AI517	600-150279	9000		SL02	11901234	SROLFE
14	123400028078	PLASTICS	BAG BARRIER NEWTEQ225X460OFFALAI517 STKD	600-155786	1000		SL02	10201234	SROLFE
15	123400028089	PLASTICS	BAG PRINT 517 460X610X25UM HD 1000RL	600-149157	1000		SL02	10201234	SROLFE
16	123400028088	CARDBOARD	BASE CHILLED LGE ME-102GL C97	600-193762	1680		SL02	11901234	SROLFE
17	123400028088	CARDBOARD	BASE CHILLED MED ME-101GL C97	600-193761	1680		SL02	11901234	SROLFE
18	123400028088	CARDBOARD	BASE CHILLED SML ME-100GL C97	600-193763	1200		SL02	11901234	SROLFE
19	123400028088	CARDBOARD	BASE FROZEN 60LB	600-149296	3200		SL02	11901234	SROLFE
20	123400028078		BASE OFFAL 60LB HOOK & EYE	600-191206	450		SL02	10201234	SROLFE
21	123400028089	CARDBOARD	CARTON OFFAL 40LB	600-191203	480		SL02	10201234	SROLFE
22	123400028089	CARDBOARD	DIVIDER 165MM CODE-38	600-190960	50		SL02	10201234	SROLFE
23	123400028093	CARTON STO	GLUE HOTMELT HEATSPEK 9000 15KG	600-222819	60		SL02	11901234	SROLFE
24	123400028084	CARDBOARD	LID CHILLED AUSSIE SH	600-190957	1200		SL02	13961234	MARK
25	123400028088	CARDBOARD	LID FROZEN BP 60LB S	600-191072	3200		SL02	11901234	SROLFE
26	123400028078		LID OFFAL AMH 60LB LG	600-191205	450		SL02	10201234	SROLFE
27	123400028089	PLASTICS	STRAPPING CLEAR 9MMX4000M RL	600-158041	1		SL02	10201234	SROLFE
28	123400028080	LAUNDRY	JACKET FRZ FGR/YELLH/VREFLT TAPE SZ 6XL	500-202624	1		SL11	13961234	NSUKWIPAD
29	123400028083	LAUNDRY-1	JACKET FRZ FGR/YELLH/VREFLT TAPE SZ XL	500-202619	1		SL11	13961234	NSUKWIPAD
30	123400028085	LAUNDRY-2	SHIRT POLO HIVIS REF PREM YEL/NAV SZ M	500-204558	1		SL11	13961234	LPRIOR
31	123400028081	LAUNDRY-2	SHIRT WHITE L/S POLY COTTON SML C-7000/C	500-259087	1		SL11	11901234	NSUKWIPAD
32	123400028082	LAUNDRY-1	TROUSER CARGO NAVY BLUE SZ 97R	500-205866	3		SL11	13961234	NSUKWIPAD
33	123400028090	LAUNDRY	TROUSERS FRZFOR GR/YELL H/V PANEL SZM	500-202628	1		SL11	13961234	LPRIOR
34	123400028081	LAUNDRY-1	TROUSERS POLY COTTON WHITE SZ 77	500-204408	1		SL11	11901234	NSUKWIPAD
35			GLOVE POLAR BEAR RED SZ 7	500-221078	2		SL11	10201234	LPRIOR
36			INSOLE BOOT SZ 6	500-208973	1		SL11	10201234	LPRIOR
37			GLOVE POLAR BEAR YELLOW SZ 8	500-221079	2		SL11	11901234	LPRIOR
38			GLOVE POLAR BEAR BLACK SZ 10	500-221081	2		SL11	10201234	LPRIOR
39			GLOVES COTTON KNITTED	500-207134	1		SL11	13001234	LPRIOR
40			KNIFE BONING 6IN WIDE 2-700-15-115W	500-204009	2		SL11	11901234	MARK
41			KNIFE POUCH WHITE DOUBLE 22CMS KP01	500-205928	1		SL11	11901234	MARK
42			STEEL **** COMBI 7-3882-25-66	500-202971	1		SL11	11901234	MARK
43			CHAIN BUTCHERS BE970	500-209270	1		SL11	11901234	MARK
44			STEEL HOLDER	500-204250	1		SL11	11901234	MARK
45			GLOVES COTTON KNITTED	500-207134	1		SL11	13961234	MARK
46			GLOVES BEAVER NAVY WITH YELLOW STIPLES	500-218872	2		SL11	13961234	MARK
47			KNIFE BONING 6IN WIDE 2-700-15-115W	500-204009	1		SL11	11901234	MARK
48			KNIFE BONING 5IN VICT PRO 2-720-13-200BL	500-229199	1		SL11	11901234	MARK
49			STEEL F **** 12IN COMBI SQUARE 7-5982-30	500-206015	1		SL11	11901234	MARK
50			GLOVE POLAR BEAR DARK BLUE SZ 9	500-221080	2		SL11	11901234	MARK
51			SLEEVE 28CM CUT-RESISTANT GREY CUFF	500-220090	1		SL11	11901234	MARK
52			GLOVE POLAR BEAR YELLOW SZ 8	500-221079	2		SL11	11901234	MARK
53			GLOVE POLAR BEAR YELLOW SZ 8	500-221079	2		SL11	11901234	MARK
54			SLEEVE 50CM CUT-RESISTANT GREY CUFF 93.	500-220092	1		SL11	11901234	MARK
55			SLEEVE 50CM CUT-RESISTANT GREY CUFF 93.	500-220092	1		SL11	11901234	MARK
56			GLOVE POLAR BEAR YELLOW SZ 8	500-221079	2		SL11	11901234	MARK
57			GLOVES BEAVER NAVY WITH YELLOW STIPLES	500-218872	2		SL11	13961234	MARK
58			SLEEVE 28CM CUT-RESISTANT GREY CUFF	500-220090	1		SL11	11901234	MARK
59			GLOVE POLAR BEAR YELLOW SZ 8	500-221079	2		SL11	11901234	MARK
60			BOOT PU GREEN BEKINA STEPLITEX SZ 9	500-269892	1		SL11	10201234	CBIRTHIS
61			GLOVES BEAVER NAVY WITH YELLOW STIPLES	500-218872	2		Sl11	13001234	LPRIOR
62			GLOVE SILVERLINED 88-350 SZ7	500-358713	60		SL11	20721234	CBIRTHIS
63			GLOVE SILVERLINED 88-350 SZ8	500-358715	144		SL11	20721234	CBIRTHIS
64			GLOVE SILVERLINED 88-350 SZ9	500-358787	96		SL11	20721234	CBIRTHIS
65			GLOVE SILVERLINED 88-350 SZ10	500-358789	36		SL11	20721234	CBIRTHIS
66			INSOLE BOOT SZ 11	500-208967	2		SL11	11901234	CBIRTHIS
67			GLOVE DISPOSABLE SHOULDER LENGTH PLASTIC	500-207504	2		SL11	11901234	CBIRTHIS
68			GLOVE NITRILE G/T TOUCH BLUE SZ L	500-267938	2		SL11	11901234	CBIRTHIS
69			BELT BUTCHER	500-210076	1		SL11	11901234	CBIRTHIS
70			GLOVES BEAVER NAVY WITH YELLOW STIPLES	500-218872	2		SL11	13961234	CBIRTHIS
71			GLOVES COTTON KNITTED	500-207134	1		SL11	13961234	CBIRTHIS
72			GLOVE DISPOSABLE SHOULDER LENGTH PLASTIC	500-207504	2		SL11	11901234	CBIRTHIS
73			GLOVE NITRILE G/T TOUCH BLUE SZ L	500-267938	2		SL11	11901234	CBIRTHIS
74			GLOVES BEAVER NAVY WITH YELLOW STIPLES	500-218872	2		SL11	500-208306	CBIRTHIS
75			APRON PVC 90 X 120 WHITE	500-208308	1		SL11	10201234	CBIRTHIS
76			GLASSES SAFETY ANTI FOG CLEAR MEVX2C	500-207507	1		SL11	11901234	LPRIOR
77			GLOVE POLAR BEAR YELLOW SZ 8	500-221079	1		SL11	10201234	LPRIOR
78			KNIFE BONING 6IN FDICK FLEX BL 8-2981-15	500-204661	2		SL11	10201234	LPRIOR
79			APRON PVC 90 X 120 RED	500-208306	1		SL11	11901234	CBIRTHIS
80			GLOVE POLAR BEAR DARK BLUE SZ 9	500-221080	4		SL11	10201234	LPRIOR
81			GLOVES COTTON KNITTED	500-207134	2		SL11	10701234	LPRIOR
82			TRAPPER GLUE BOARD (PACK 12) TR2712	500-204626	4		SL11	20001234	SROLFE
WMS 201

Cell Formulas
Range	Formula
G2:G34	G2	=IF(COUNTIF(D2,{"500*"}), "SL11", IF(COUNTIF(D2,{"600*"}), "SL02", IF(COUNTIF(D2,{"801*"}), "MS01", IF(COUNTIF(D2,{"800*"}), "MS01"))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G1	Cell Value	="SL02"	text	NO
G1	Cell Value	="SL11"	text	NO
A1	Cell Value	duplicates	text	NO
G2:G1048576	Cell Value	="SL02"	text	NO
G2:G1048576	Cell Value	="SL11"	text	NO
G3	Cell Value	="SL11"	text	NO
A2:A83,A85:A1048576	Cell Value	duplicates	text	NO

 

[hepkat63]

above is the sheet that I paste into (this has already been done in the example above)

 

[hepkat63]

Here is the 2nd spreadsheet.

 

[hepkat63]

anyone?

 

[Colo]

Hi,
Since the "issues" worksheet has been posted as an image (not via XLBB), to test this, we need to type data manually. Please accept that I didn't test it with actual sample data, there might be a misunderstanding.

Sub Sample1()
    Dim shDLSAP As Worksheet    'SAP worksheet
    Dim shIssues As Worksheet    'Issues worksheet
    Dim shTemp As Worksheet    'Temporary helper worksheet
    Dim dtCnt As Long

    Application.ScreenUpdating = False

    Set shDLSAP = Sheets("Result")    'Change here to suit actual sheet name
    Set shIssues = Sheets("issues")    'Change here to suit actual sheet name
    Set shTemp = Sheets.Add    'Making a temporary helper wks

    'Filter by the date between Yesterday and Today
    shIssues.Activate
    shIssues.AutoFilterMode = False
    With shIssues.UsedRange
        .AutoFilter Field:=6, Criteria1:=Format(Date - 1, Range("F2").NumberFormatLocal), Operator:=xlOr, Criteria2:=Format(Date, Range("F2").NumberFormatLocal)
        .Range(.Range("A2"), .Cells(Rows.Count, "G").End(xlUp)).Copy shTemp.Range("A1")
    End With
    'Counting fildtered data
    dtCnt = shIssues.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
    shIssues.AutoFilterMode = False

    'Data transfer
    With shDLSAP.Cells(Rows.Count, 1).End(xlUp).Offset(1)
        .Offset(, 2).Resize(dtCnt).Value = shTemp.Range("G1").Resize(dtCnt).Value
        .Offset(, 3).Resize(dtCnt).Value = shTemp.Range("A1").Resize(dtCnt).Value
        .Offset(, 4).Resize(dtCnt).Value = shTemp.Range("B1").Resize(dtCnt).Value
        .Offset(, 6).Resize(dtCnt).Value = shTemp.Range("C1").Resize(dtCnt).Value
        .Offset(, 7).Resize(dtCnt).Value = shTemp.Range("D1").Resize(dtCnt).Value
        .Offset(, 8).Resize(dtCnt).Value = shTemp.Range("E1").Resize(dtCnt).Value
    End With

    'Deleting the helper worksheet
    Application.DisplayAlerts = False
    shTemp.Delete
    Application.DisplayAlerts = True

    shDLSAP.Select
    Application.ScreenUpdating = True
End Sub

 

[hepkat63]

thank you - much appreciated. I'll give it a try and let you know.

 

[hepkat63]

Ok, so I've just had a chance to try this, but it bombs out on line 7. I've changed the sheet name as it says to in the comment, opened that sheet and then ran F8 to step into, but it fails.

Sub Sample1()
Dim shDLSAP As Worksheet 'SAP worksheet
Dim shIssues As Worksheet 'Issues worksheet
Dim shTemp As Worksheet 'Temporary helper worksheet
Dim dtCnt As Long

Application.ScreenUpdating = False

Set shDLSAP = Sheets("GoodsIssuedToSap_20220829_131456.xlsx") 'Change here to suit actual sheet name
Set shIssues = Sheets("store tally report - MASTER.xlsm") 'Change here to suit actual sheet name
Set shTemp = Sheets.Add 'Making a temporary helper wks

'Filter by the date between Yesterday and Today
shIssues.Activate
shIssues.AutoFilterMode = False
With shIssues.UsedRange
.AutoFilter Field:=6, Criteria1:=Format(Date - 1, Range("F2").NumberFormatLocal), Operator:=xlOr, Criteria2:=Format(Date, Range("F2").NumberFormatLocal)
.Range(.Range("A2"), .Cells(Rows.Count, "G").End(xlUp)).Copy shTemp.Range("A1")
End With
'Counting fildtered data
dtCnt = shIssues.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1
shIssues.AutoFilterMode = False

'Data transfer
With shDLSAP.Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Offset(, 2).Resize(dtCnt).Value = shTemp.Range("G1").Resize(dtCnt).Value
.Offset(, 3).Resize(dtCnt).Value = shTemp.Range("A1").Resize(dtCnt).Value
.Offset(, 4).Resize(dtCnt).Value = shTemp.Range("B1").Resize(dtCnt).Value
.Offset(, 6).Resize(dtCnt).Value = shTemp.Range("C1").Resize(dtCnt).Value
.Offset(, 7).Resize(dtCnt).Value = shTemp.Range("D1").Resize(dtCnt).Value
.Offset(, 8).Resize(dtCnt).Value = shTemp.Range("E1").Resize(dtCnt).Value
End With

'Deleting the helper worksheet
Application.DisplayAlerts = False
shTemp.Delete
Application.DisplayAlerts = True

shDLSAP.Select
Application.ScreenUpdating = True
End Sub

 

[hepkat63]

Here is the goods issued sheet:

GoodsIssuedToSap_20220829_131456.xlsx
	A	B	C	D	E	F	G	H	I
1	MaintenanceTransactionNumber	Location	Description	SAPMaterialNumber	Quantity	UnE	SLoc	CostCentre	UserViewShort
2	123400028203	V1-1	BATTEN WEATHERPROOF LED SL9721-35W DL	800-949604	2		MS01	13001234	BDEMAMIEL
3	123400028203	H10-3	CABLE RTD 1500MM CH12265	801-060647	1		MS01	13001234	BDEMAMIEL
4	123400028194	H10-3	CONNECTOR TURCK FS4.4 1825-05	800-748003	2		MS01	10201234	BDEMAMIEL
5	123400028196		CONTACT NORMALLY CLOSED TEL ZB4BZ102	800-797066	1		MS01	11901234	RBRITTS
6	123400028180	HC13-2	ENCLOSURE MTG 2G 198X101X63 56E2RO	800-749845	1		MS01	13001234	RBRITTS
7	123400028197	K4-4	HOLDER SPRING CONTACT Y2702416	801-016449	3		MS01	10201234	BDEMAMIEL
8	123400028187	SMC	JOINER STRT PUSHFIT 10MMX10MM KQ2H10-00A	801-063676	1		MS01	11901234	PHINEAS
9	123400028205	B3-3	KIT WEAR DNC-80-PPV-A FESTO 369199	800-800989	1		MS01	10201234	SJONES
10	123400028189		MODULE FLEX ROLLER CRG16801V	800-883219	1		MS01	11901234	BRUCE
11	123400028201	J8-2	NYLON STEEL HOLDER	800-800077	4		MS01	11901234	BRUCE
12	123400028204	HB0-2	PLUG 2 FLAT + ROUND EARTH 10A - CLIPSAL	800-786050	1		MS01	13001234	DMICKAN
13	123400028178	HB0-2	PLUG STRAIGHT 3 FLAT PIN 10A - CLIPSAL	800-786054	1		MS01	13001234	TRIDDELL
14	123400028196	H3-2	PUSHBUTTON EMER STOP RED ZB4BS844	800-757208	1		MS01	11901234	RBRITTS
15	123400028182	FK	REFLECTOR IFM 18MM PE (OGP200)	800-757453	1		MS01	13001234	RBRITTS
16	123400028181	H5-4	SENSOR PHOTOELECTRIC VT180-P440 6008793	800-760104	1		MS01	11901234	BRIGHTWELL
17	123400028180	HC12-3	SOCKET COMB PIN 10A LESS ENCL 56C310LERO	800-760768	2		MS01	13001234	RBRITTS
18	123400028186	L6-2	SPROCKET S800 132MMD 8T ANG 40MM SQBORE	800-761679	1		MS01	10201234	INNOCENT
19	123400028180	HC11-2	SWITCH 250V 10A 2WAY 56SW110/2LERO	800-786434	1		MS01	13001234	RBRITTS
20	123400028185		WIRE BRUSH S/S 4 ROW PLASTIC HANDLE	800-765716	1		MS01	10201234	INNOCENT
21	123400028183		X-DELETE	800-870877	1		MS01	11901234	CONTRACTOR
22	123400028202	PLASTICS	BAG PRINT 517 460X800X25UM HD 750RL	600-149160	1500		SL02	11901234	SROLFE
23	123400028200	CARDBOARD	BASE CHILLED LGE ME-102GL C97	600-193762	1680		SL02	11901234	SROLFE
24	123400028200	CARDBOARD	BASE CHILLED MED ME-101GL C97	600-193761	840		SL02	11901234	SROLFE
25	123400028200	CARDBOARD	BASE CHILLED SML ME-100GL C97	600-193763	600		SL02	11901234	SROLFE
26	123400028200	CARDBOARD	BASE FROZEN 60LB	600-149296	3200		SL02	11901234	SROLFE
27	123400028202	LABELS	INSERT WAGYU BLACK MBS7-8 140X100 1000RL	600-193840	3000		SL02	11901234	SROLFE
28	123400028199	LABELS	LABEL BLANK LSWI001 CHINA 76X48	600-150060	46000		SL02	11901234	SROLFE
29	123400028202		LABEL THERMAL RED BORDER 77X124 1200RL	600-149150	14400		SL02	11901234	SROLFE
30	123400028190	CARDBOARD	LID CHILLED AMH WHITE	600-191209	1200		SL02	13961234	SROLFE
31	123400028192	CARDBOARD	LID CHILLED RIV BLACK ANG PP	600-191422	1200		SL02	13961234	SROLFE
32	123400028179	CARDBOARD	LID CHILLED THOUSAND	600-191207	1200		SL02	13961234	MARK
33	123400028200	CARDBOARD	LID FROZEN BP 60LB S	600-191072	3200		SL02	11901234	SROLFE
34	123400028202	PLASTICS	LINER BLUE 540+380x580x90UM LD 150/RL	600-167921	50		SL02	11901234	SROLFE
35	123400028202		BAG POLY 205LT NATURAL 11489	500-223693	1		SL11	11901234	SROLFE
36	123400028198	LAUNDRY-2	DUSTCOAT WHITE YELLOW HIVIS P/COT SZ 6	500-244698	1		SL11	11901234	LPRIOR
37	123400028188	LAUNDRY-3	JACKET RAIN H/V LIME/YELLNY SZ 4XL	500-202702	1		SL11	11901234	LPRIOR
38	123400028195	LAUNDRY-2	JACKET RAIN H-V LIME/YELLNY SZ XL	500-202699	1		SL11	13001234	LPRIOR
39	123400028191	LAUNDRY-2	SHIRT WHITE L/S POLY COTTON 3XL C-7000/C	500-259093	1		SL11	11901234	LPRIOR
40	123400028191	LAUNDRY-2	SHIRT WHITE L/S POLY COTTON MED C-7000/C	500-259089	1		SL11	11901234	LPRIOR
41	123400028193		TREATMENT BOILER ALKALI 15L 356.11H	500-255600	2		SL11	13141234	WRAMPONI
42	123400028184	LAUNDRY-1	TROUSERS POLY COTTON WHITE SZ 87	500-204410	1		SL11	11901234	CBIRTHIS
43			GLOVE POLAR BEAR RED SZ 7	500-221078	2		SL11	10201234	NSUKWIPAD
44			STEEL **** COMBI 7-3882-25-66	500-202971	1		SL11	10201234	MARK
45			GLOVE SILVERLINED 88-350 SZ7	500-358713	12		SL11	10201234	MARK
46			GLOVE SILVERLINED 88-350 SZ8	500-358715	24		SL11	10201234	MARK
47			GLOVE POLAR BEAR DARK BLUE SZ 9	500-221080	1		SL11	11901234	MARK
48			BOOT PU GREEN BEKINA STEPLITEX SZ 9	500-269892	1		SL11	11901234	MARK
49			GLASSES SAFETY ANTI FOG CLEAR MEVX2C	500-207507	1		SL11	11901234	MARK
50			GLOVES FREEZER FUR LINED	500-207227	1		SL11	13961234	MARK
51			GLOVE POLAR BEAR DARK BLUE SZ 9	500-221080	2		SL11	11901234	MARK
52			GLOVE POLAR BEAR BLACK SZ 10	500-221081	2		SL11	11901234	NSUKWIPAD
53			EARMUFFS PELTOR H7P3E CLIP-ON	500-218812	1		SL11	11901234	NSUKWIPAD
54			GLASSES SAFETY ANTI FOG CLEAR MEVX2C	500-207507	1		SL11	11901234	NSUKWIPAD
55			GLOVE SILVERLINED 88-350 SZ7	500-358713	60		SL11	20721234	CBIRTHIS
56			GLOVE SILVERLINED 88-350 SZ8	500-358715	144		SL11	20721234	CBIRTHIS
57			GLOVE SILVERLINED 88-350 SZ9	500-358787	96		SL11	20721234	CBIRTHIS
58			GLOVE SILVERLINED 88-350 SZ10	500-358789	24		SL11	20721234	CBIRTHIS
59			GLOVE DISPOSABLE SHOULDER LENGTH PLASTIC	500-207504	2		SL11	11901234	CBIRTHIS
60			GLOVE NITRILE G/T TOUCH BLUE SZ L	500-267938	2		SL11	11901234	CBIRTHIS
61			GLOVES BEAVER NAVY WITH YELLOW STIPLES	500-218872	2		SL11	13961234	CBIRTHIS
62			GLOVES BEAVER NAVY WITH YELLOW STIPLES	500-218872	2		SL11	13961234	CBIRTHIS
63			GLOVE DISPOSABLE SHOULDER LENGTH PLASTIC	500-207504	2		SL11	11901234	CBIRTHIS
64			GLOVE NITRILE G/T TOUCH BLUE SZ L	500-267938	2		SL11	11901234	CBIRTHIS
65			APRON PVC 90 X 120 WHITE	500-208308	1		SL11	10201234	CBIRTHIS
66			GLOVE NITRILE G/T TOUCH BLUE SZ L	500-267938	2		SL11	10201234	CBIRTHIS
67			CARTRIDGES GREEN STUNNER BX1000	500-208905	20		SL11	10201234	CBIRTHIS
68			O RING WHITE LARGE 'D' 15MM	500-267925	500		SL11	10201234	CBIRTHIS
69			APRON PVC 90 X 120 WHITE	500-208308	1		SL11	10201234	SROLFE
70			APRON PVC 90 X 120 WHITE	500-208308	1		SL11	10201234	SROLFE
71			GLOVES NITTY GRITTY (12PR BOX)	500-206496	1		SL11	10201234	LPRIOR
72			SQUEEGEE RED 600MM	500-204244	1		SL11	11901234	LPRIOR
73			COUNTER TALLY DESKTOP 2 BANK 4 DIGIT D2	500-264285	1		SL11	10201234	SROLFE
74			GLOVE POLAR BEAR DARK BLUE SZ 9	500-221080	1		SL11	11901234	LPRIOR
75			GLASSES SAFETY ANTI FOG CLEAR MEVX2C	500-207507	1		SL11	13001234	LPRIOR
76			GLOVES BEAVER NAVY WITH YELLOW STIPLES	500-218872	2		SL11	13961234	LPRIOR
77			GLOVES COTTON KNITTED	500-207134	1		SL11	13961234	LPRIOR
78			APRON PVC 90 X 120 WHITE	500-208308	1		SL11	13001234	LPRIOR
79			GLOVE GREEN PVC DOUBLE DIP 45CM	500-229077	2		SL11	13001234	LPRIOR
WMS 201

Cell Formulas
Range	Formula
G2:G42	G2	=IF(COUNTIF(D2,{"500*"}), "SL11", IF(COUNTIF(D2,{"600*"}), "SL02", IF(COUNTIF(D2,{"801*"}), "MS01", IF(COUNTIF(D2,{"800*"}), "MS01"))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G1	Cell Value	="SL02"	text	NO
G1	Cell Value	="SL11"	text	NO
A1	Cell Value	duplicates	text	NO
G2:G1048576	Cell Value	="SL02"	text	NO
G2:G1048576	Cell Value	="SL11"	text	NO
G3	Cell Value	="SL11"	text	NO
A2:A80,A82:A1048576	Cell Value	duplicates	text	NO