Help me automate these tasks between sheets

[JohnnyBeGood]

Hi all,

I'm hoping someone will be able to help me automate this task. Currently, I'm doing it manually and its time-consuming. I'm sure there's a better way of doing it, but I don't know.
Attached are sample files. First workbook has two sheets MASTER_LIST and Sheet1 and final_merge is how information needs to be merged. The account is in 000902-000 format, and it needs to be 902
Only accounts appearing on Sheet1 need to be filtered, and the master_list file contains around 10,000 lines. If all fields with numbers are empty ACTION NEEDED needs to be populated.
Also, master_list gets exported weekly, and new information is pulled. Please let me know if more clarification is needed.

Thanks in advance!

master_list.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Customer Num	last_name	first_name	addr_care_of	addr_1	city	state	zip	email	home_phone	bus_phone	cell_phone
2	------------	---------	----------	------------	------	----	-----	---	-----	----------	---------	----------
3	3	JOHN	DOE		16619 SE 1ST AVE	BIG CITY	NY	550429105	johndoe@mail.com	5061231234
4	1002	CHATEAU BUSINESS PARK-FC/ABCD LLC			16620 SE 1ST AVE	BIG CITY	NY	550429145		5061231235	5061231254
5	147555	MIKE	WINTERS	ABC CORP	16621 SE 1ST AVE	BIG CITY	NY	550422933
6	137545	JOHNNY	GOOD		16622 SE 1ST AVE	BIG CITY	NY	550429105		5061231237
7	902	LUCY	DOE		16633 SE 1ST AVE	BIG CITY	NY	550429100	lucydoe@mail.com; doelucy25@mail2.com
8	127555	STEVE	SUMMER	123 CORP	16656 SE 1ST AVE	BIG CITY	NY	550449105		5061231231		5061231265
MASTER_LIST

master_list.xlsx
	A	B	C
1	ACCOUNT NUMBER	NAME	ADDRESS
2	001002-000	CHATEAU BUSINESS PAR	16620 SE 1ST AVE
3	29261052
4	issue2
5	147555-000	MIKE WINTERS	16621 SE 1ST AVE
6	34713349
7	issue1
8	000902-000	LUCY DOE	16633 SE 1ST AVE
9	33462540
10	issue2
11	127555-000	STEVE SUMMER	16656 SE 1ST AVE
12	34409077
13	issue1
Sheet1

final_merge.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	ACCOUNT NUMBER	NAME	addr_care_of	ADDRESS	EMAIL	PHONE TO CALL	bus_phone	PHONE TO TEXT	DEVICE ID	ACTION NEEDED	PROBLEM
2	3	JOHN DOE		16619 SE 1ST AVE	johndoe@mail.com	5061231234			29261052		issue2
3	147555	MIKE WINTERS	ABC CORP	16621 SE 1ST AVE					34713349	No contact, mail letter	issue1
4	902	LUCY DOE		16633 SE 1ST AVE	lucydoe@mail.com; doelucy25@mail2.com				33462540	No contact, mail letter	issue2
5	127555	STEVE SUMMER	123 CORP	16656 SE 1ST AVE		5061231231		5061231265	34409077		issue1
final_merge

 

[Ottsel]

Just to clarify; the 'MASTERLIST' will be different each week, therefore no formatting is required, but rather sheet1 & final merge is where you require automation, correct?

 

[JohnnyBeGood]

Thanks for the reply!

Correct. Columns in MASTER_LIST will not change only phone numbers and emails will be updated. New lines will be added to the bottom as new customers are added.
Information on Sheet1 will have new information each week that will need to be matched against MASTER_LIST and added to final_merge. Let me know if you need more clarification.

 

[Ottsel]

Sheet1 is throwing me a curveball with that "issue" being listed under 'ACCOUNT NUMBER' and their 'Device ID'. Is that just how your system exports data?

Also, does sheet1 contain other columns of data such as Email, Phone to Call, bus_phone, etc.?

 

[JohnnyBeGood]

Sheet1 comes from a different system, and the data is really "dirty" with a bunch of empty spaces and some other data that is unnecessary. On top of that, where ie. account number, device id, issue1, issue2 appear is not consistent.
The only way I could clean it up is by copying the first 3 columns and doing "Format as table" and then unchecking unnecessary info, which gives me some consistency at the end.
Here is a sample of how it comes before cleaning it up:

RAW Cycle 11 and 31 Endpoint Tampers Report.xls
	A	B	C
1
2
3
4
5
6	ACCOUNT NUMBER	NAME	ADDRESS
7	001002-000	CHATEAU BUSINESS PAR	16620 SE 1ST AVE
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24	29261052
25	W
26
27			00
28
29
30
31
32	COUNTS/FLAG:	1: 1	/ 2: 0
33
34	issue2
35
36
37
38
39
40	147555-000	MIKE WINTERS	16621 SE 1ST AVE
Sheet1

 

[Ottsel]

Appreciate the secondary screenshot of ‘Sheet1’.

Going off the ‘MASTER_LIST I believe there’s an error with the first and last name columns, so I switched it on my end – the names, not the actual columns, but just alter the formulas if I’m mistaken.

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Customer Num	last_name	first_name	addr_care_of	addr_1	city	state	zip	email	home_phone	bus_phone	cell_phone
2	------------	------------	------------	------------	------------	------------	------------	------------	------------	------------	------------	------------
3	3	DOE	JOHN		16619 SE 1ST AVE	BIG CITY	NY	550429105	johndoe@mail.com	5061231234
4	1002		CHATEAU BUSINESS PARK-FIC/ABCD LLC		16620 SE 1ST AVE	BIG CITY	NY	550429145		5061231235
5	147555	WINTERS	MIKE	ABC CORP	16621 SE 1ST AVE	BIG CITY	NY	550422933
6	137545	GOOD	JOHNNY		16622 SE 1ST AVE	BIG CITY	NY	550429105		5061231237	5061231254
7	902	DOE	LUCY		16633 SE 1ST AVE	BIG CITY	NY	550429100	lucydoe@mail.acom; doelucy25@mail2.com
8	127555	SUMMER	STEVE	123 CORP	16656 SE 1ST AVE	BIG CITY	NY	550449105		5061231231		5061231265
MASTER_LIST

Since you are only using columns A:C I went ahead and inserted a D & E column to help with your ‘final_merge’ The pattern appears to be the 'Device ID' is always second and the 'issue#' is third, so taking this simple cell reference and it being a relative reference should give you what you seek.
Note: you'll still need to sort your data into the first example (sheet1). I attempted to construct a VBA code to help with this, but my knowledge of VBA is still very... novice, and struggled with the 8 digit formatting.

Book1.xlsm
	A	B	C	D	E
1	ACCOUNT NUMBER	NAME	ADDRESS	DEVICE ID	PROBLEM
2	000003-000	JOHN DOE	16619 SE 1ST AVE	29261052	issue2
3	29261052			issue2	001002-000
4	issue2			001002-000	34713349
5	001002-000	CHATEAU BUSINESS PARK-FIC/ABCD LLC	16620 SE 1ST AVE	34713349	issue1
6	29261052			issue2	001002-000
7	issue2			001002-000	34713349
8	001002-000	MIKE WINTERS	16621 SE 1ST AVE	34713349	issue1
9	34713349			issue1	000902-000
10	issue1			000902-000	33462540
11	000902-000	LUCY DOE	16633 SE 1ST AVE	33462540	issue2
12	33462540			issue2	127555-000
13	issue2			127555-000	34409077
14	127555-000	STEVE SUMMER	16656 SE 1ST AVE	34409077	issue1
15	34409077			issue1	0
16	issue1			0	0
Sheet1

Cell Formulas
Range	Formula
D2:D5	D2	=A6
E2:E5	E2	=A7
D6:D16	D6	=A7
E6:E16	E6	=A8

I did a string of vlookups to format your ‘final_merge’ sheet. I believe you’re using 2 workbooks, but I made it just another sheet on my end to test it.

Convert your ‘final_merge’ into a table and use the following layout:

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	ACCOUNT NUMBER	NAME	add_care_of	ADDRESS	EMAIL	PHONE TO CALL	bus_phone	PHONE TO TEXT	DEVICE ID	ACTION NEEDED	PROBLEM
2	3	JOHN DOE		16619 SE 1ST AVE	johndoe@mail.com	5061231234			29261052		issue2
3
4
5	1002	CHATEAU BUSINESS PARK-FIC/ABCD LLC		16620 SE 1ST AVE		5061231235			34713349		issue1
6
7
8	1002	MIKE WINTERS		16620 SE 1ST AVE		5061231235			34713349		issue1
9
10
11	902	LUCY DOE		16633 SE 1ST AVE	lucydoe@mail.acom; doelucy25@mail2.com				33462540	No Contact	issue2
12
13
14	127555	STEVE SUMMER	123 CORP	16656 SE 1ST AVE		5061231231		5061231265	34409077		issue1
final_merge

Cell Formulas
Range	Formula
A2:A14	A2	=IF(B2<>0,VALUE(LEFT(Sheet1!A2,6)),"")
B2:B14	B2	=Sheet1!B2
C2:C14	C2	=IFERROR(VLOOKUP(A2,MASTER_LIST!A:L,4,FALSE),"")
D2:D14	D2	=IFERROR(VLOOKUP(A2,MASTER_LIST!A:L,5,FALSE),"")
E2:E14	E2	=IFERROR(VLOOKUP(A2,MASTER_LIST!A:L,9,FALSE),"")
F2:F14	F2	=IFERROR(VLOOKUP(A2,MASTER_LIST!A:L,10,FALSE),"")
G2:G14	G2	=IFERROR(VLOOKUP(A2,MASTER_LIST!A:L,11,FALSE),"")
H2:H14	H2	=IFERROR(VLOOKUP(A2,MASTER_LIST!A:L,12,FALSE),"")
I2:I14	I2	=IFERROR(VLOOKUP(B2,Sheet1!B:E,3,FALSE),"")
J2:J14	J2	=IF(OR(F2>0,G2>0,H2>0),"","No Contact")
K2:K14	K2	=IFERROR(VLOOKUP(B2,Sheet1!B:E,4,FALSE),"")

After that all you’ll need to do is go into Cell A1 and uncheck ‘Blanks’ and you’ll see all the data you’re after.

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	ACCOUNT NUMBER	NAME	add_care_of	ADDRESS	EMAIL	PHONE TO CALL	bus_phone	PHONE TO TEXT	DEVICE ID	ACTION NEEDED	PROBLEM
2	3	JOHN DOE		16619 SE 1ST AVE	johndoe@mail.com	5061231234			29261052		issue2
5	1002	CHATEAU BUSINESS PARK-FIC/ABCD LLC		16620 SE 1ST AVE		5061231235			34713349		issue1
8	1002	MIKE WINTERS		16620 SE 1ST AVE		5061231235			34713349		issue1
11	902	LUCY DOE		16633 SE 1ST AVE	lucydoe@mail.acom; doelucy25@mail2.com				33462540	No Contact	issue2
14	127555	STEVE SUMMER	123 CORP	16656 SE 1ST AVE		5061231231		5061231265	34409077		issue1
final_merge

Cell Formulas
Range	Formula
A2,A5,A8,A11,A14	A2	=IF(B2<>0,VALUE(LEFT(Sheet1!A2,6)),"")
B2,B5,B8,B11,B14	B2	=Sheet1!B2
C2,C5,C8,C11,C14	C2	=IFERROR(VLOOKUP(A2,MASTER_LIST!A:L,4,FALSE),"")
D2,D5,D8,D11,D14	D2	=IFERROR(VLOOKUP(A2,MASTER_LIST!A:L,5,FALSE),"")
E2,E5,E8,E11,E14	E2	=IFERROR(VLOOKUP(A2,MASTER_LIST!A:L,9,FALSE),"")
F2,F5,F8,F11,F14	F2	=IFERROR(VLOOKUP(A2,MASTER_LIST!A:L,10,FALSE),"")
G2,G5,G8,G11,G14	G2	=IFERROR(VLOOKUP(A2,MASTER_LIST!A:L,11,FALSE),"")
H2,H5,H8,H11,H14	H2	=IFERROR(VLOOKUP(A2,MASTER_LIST!A:L,12,FALSE),"")
I2,I5,I8,I11,I14	I2	=IFERROR(VLOOKUP(B2,Sheet1!B:E,3,FALSE),"")
J2,J5,J8,J11,J14	J2	=IF(OR(F2>0,G2>0,H2>0),"","No Contact")
K2,K5,K8,K11,K14	K2	=IFERROR(VLOOKUP(B2,Sheet1!B:E,4,FALSE),"")

 

[JohnnyBeGood]

Thank you so much for this!
I'm away from the computer now and will try it out and post back results.

 

[Ottsel]

Looks like my example had a little typo I overlooked.
On sheet1 I really wanted to make it so you didn't have to manually delete rows and sort it, since there appears to be a pattern from what I'm seeing.

threw together this random bit:

Book1.xlsm
	A	B	C	D	E
1	ACCOUNT NUMBER	NAME	ADDRESS	DEVICE ID	PROBLEM
2	000003-000	JOHN DOE	16619 SE 1ST AVE	29261052	issue2
3	29261052
4	issue2
5	001002-000	CHATEAU BUSINESS PARK-FIC/ABCD LLC	16620 SE 1ST AVE
6	29261052
7	issue2
8
9
10
11
12
13
14
15	f
16
17
18
19
20
21
22	s
23
24
25
26
27
28
29	2
30
31
32
33
34
35	random
36
37
38
39	001002-000	MIKE WINTERS	16621 SE 1ST AVE
40	34713349
41	issue1
42	000902-000	LUCY DOE	16633 SE 1ST AVE
43	33462540
44	issue2
45	127555-000	STEVE SUMMER	16656 SE 1ST AVE
46	34409077
47	issue1
Sheet1

Cell Formulas
Range	Formula
D2	D2	=A3
E2	E2	=A4

when you run this code:

Sub CleanUpSelect1()
    Dim RowA As Range
    Dim cRng As Range
    Dim iCounter As Long

    Set RowA = Sheet1.Range("A2:A" & Sheets("Sheet1").UsedRange.Rows.Count)
    iCounter = 1
    Do
        Set cRng = RowA(iCounter, 1) 'Loop rows
        If CStr(cRng.Value2) = vbNullString Then
            cRng.EntireRow.Delete
        Else
            If Not KeepRow(cRng.Value2) Then
                cRng.EntireRow.Delete  'Delete row
            Else
                iCounter = iCounter + 1 'Advance
            End If
        End If
    Loop While iCounter <= RowA.Rows.Count 'Stop after loop all rows
End Sub
Private Function KeepRow(RowValue As String) As Boolean
    KeepRow = (RowValue Like "########" Or RowValue Like "issue#" Or RowValue Like "######-###")
End Function

It'll format it to this:

Book1.xlsm
	A	B	C	D	E
1	ACCOUNT NUMBER	NAME	ADDRESS	DEVICE ID	PROBLEM
2	000003-000	JOHN DOE	16619 SE 1ST AVE	29261052	issue2
3	29261052
4	issue2
5	001002-000	CHATEAU BUSINESS PARK-FIC/ABCD LLC	16620 SE 1ST AVE
6	29261052
7	issue2
8	001002-000	MIKE WINTERS	16621 SE 1ST AVE
9	34713349
10	issue1
11	000902-000	LUCY DOE	16633 SE 1ST AVE
12	33462540
13	issue2
14	127555-000	STEVE SUMMER	16656 SE 1ST AVE
15	34409077
16	issue1
Sheet1

Cell Formulas
Range	Formula
D2	D2	=A3
E2	E2	=A4

After that all you have to do is select the two simple references in D&E and double click them to copy it all the way down:

Book1.xlsm
	A	B	C	D	E
1	ACCOUNT NUMBER	NAME	ADDRESS	DEVICE ID	PROBLEM
2	000003-000	JOHN DOE	16619 SE 1ST AVE	29261052	issue2
3	29261052			issue2	001002-000
4	issue2			001002-000	29261052
5	001002-000	CHATEAU BUSINESS PARK-FIC/ABCD LLC	16620 SE 1ST AVE	29261052	issue2
6	29261052			issue2	001002-000
7	issue2			001002-000	34713349
8	001002-000	MIKE WINTERS	16621 SE 1ST AVE	34713349	issue1
9	34713349			issue1	000902-000
10	issue1			000902-000	33462540
11	000902-000	LUCY DOE	16633 SE 1ST AVE	33462540	issue2
12	33462540			issue2	127555-000
13	issue2			127555-000	34409077
14	127555-000	STEVE SUMMER	16656 SE 1ST AVE	34409077	issue1
15	34409077			issue1	0
16	issue1			0	0
Sheet1

Cell Formulas
Range	Formula
D2:D16	D2	=A3
E2:E16	E2	=A4

Still appears kind of "dirty", but the formulas on the 'final_merge' will work. If you're getting #REF after using it, just take the top row that's still formatted correctly, select it all 'A2:K2' and double click the lower right-hand corner and it'll fix itself. Then just sort column A to hide blanks and you 'should' have a nice list.

Heads up: the sheet1 formula is kind of slow. Maybe someone here can see the code and can speed it up. Formulas I'm alright with, and VBA, still a rookie.

 

[JohnnyBeGood]

Ok, so I tried to follow your last post suggestion with my real data and I ran into some issues.
BTW, before I start VBA Code is great! Much easier than "Format table as" and unchecking unnecessary info.

I think it has some issues with "/" when it's inside the name and address ie.
Name Address
MIKE SCHIOL DIST / K 81305 SE MALCOM WAY/KG HD

Another issue is that formula looks for if the name matches, some names can be the same just different addresses. Only what is unique with every customer is the account number, which never can be the same. From one system account number comes as 001002-000 and from the other as 1002. Can we somehow instead look up account numbers and format it the same way as 1002 format?

I really like KeepRow = (RowValue Like "########" Or RowValue Like "issue#" Or RowValue Like "######-###") because I have total of 3 issues that have unique names and I can add them to that filter ie. Or RowValue Like "issue3".

For some reason once code runs it leaves two lines one the bottom, one starts with GRAND TOTALS: and other User Nick: if you or someone else could remove based on how each line starts that would be awesome.
Obviously, putting DEVICE ID and PROBLEM on its own columns and deleting the rest below would be the best

ACCOUNT NUMBER NAME ADDRESS DEVICE ID PROBLEM
000003-000 JOHN DOE 16619 SE 1ST AVE 29261052 issue2
001002-000 MIKE WINTERS 16621 SE 1ST AVE 34713349 issue1