Editing Data and Comparing lists

[mesolomo]

I'm currently working on sorting a column of data from a PDF file into separate columns to later compare to another sheet. I'm having a few problems getting the data into a workable format, and also don't know how to compare to the other file. These are the specific issues, if someone can help me!

Ultimately, I want the data in columns that look like this:
Name, City, State, Zip, $123.00, mm/dd/yy

Currently it looks like this:

A
1 Name
2 City State Zip
3 $123.00 mm/dd/yy

The sheets I'm working with are thousands of rows long, with these three rows of data repeating for every person. I have two problems with them, however:

1. I ultimately need each of those pieces of information into a seperate cell, in separate columns. I've been transposing the data then doing "Text to Columns" to get it from three separate rows in Column A to one row in Columns A, B, C etc.

The problem with this is the names aren't always uniform. Sometimes there's Mr. or Mrs. in front of them, sometimes they have a middle initial, or an "esq." behind them, which completely messes up the "Text to Columns." Is there a better way to break them up into separate cells?


2. Secondly, not all of the names are individuals- some are businesses or charities. I want to remove all of these (and the two lines of data that go with each of them), so I only have individual people. Do you have any idea of how to make excel know the difference between, for example, "Mr. John Smith" and "Smith and Sons Realty"?

3. Lastly Once I have all of this data spread out into the different columns
(Last Name, First Name, City, State, Zip, Date, Amount), I need to compare it to another list. Basically, I've got one list of data configured in a similar way, and I want to identify any repeats between the two lists. I know that excel has a way to delete repeats, but what I need is a way to delete everything BUT repeats. Any ideas?

Thank you for your help!

 

[hiker95]

mesolomo,

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[mesolomo]

Here is what I've got now:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Dr. Nancy Petersmeyer, Physician
2	Philadelphia, PA 19118-4030
3	$500.00 5/2/2007
4	Jennifer Jean Rickard, Educator
5	Bryn Mawr, PA 19010-2859
6	$500.00 5/2/2007
7	Mr. Alan Bowser, Financial Services
8	Bedford, NY 10506-1521
9	$2,500.00 5/2/2007
10	Mr. Alan Sandman, Physician
11	Philadelphia, PA 19103-6607
12	$1,000.00 5/2/2007
13	Mr. Bruce Foulke, CEO
14	Philadelphia, PA 19115-1603
15	$500.00 5/2/2007
16	Mr. Bruce Koch, CPA
17	Philadelphia, PA 19119-1846
18	$500.00 5/2/2007
19	Mr. Cameron MacTavish, Architect
20	Philadelphia, PA 19144-3929
21	$500.00 5/2/2007
22	Mr. David Hayne, Manager
23	Philadelphia, PA 19103-6522
24	$1,500.00 5/2/2007
25	Mr. David Lipson, President & Publisher
26	Bryn Mawr, PA 19010-1648
27	$2,500.00 5/2/2007
28	Mr. David Pincus, CEO
29	Philadelphia, PA 19106
30	$500.00 5/2/2007
31	Mr. David Stone, Higher Educatoin
32	New York, NY 10025-2051
33	$500.00 5/2/2007
2007-8

What I want it to look like is:

Excel Workbook
	A	B	C	D
1	Mittal	Anand	Lower Gwynedd, PA 190022034	$2,600.00 6/6/2008
2	Quackenbos	Barbara	Maplewood,J 070402708	$300.00 6/6/2008
3	Messerman	Robert	Philadelphia, PA 191073609	$1,000.00 6/10/2008
4	Zwillman	Blair	Woodbridge,J 070951155	$100.00 6/6/2008
5	Tinkelman	Carl	Philadelphia, PA 191036218	$100.00 6/6/2008
6	Butler	Christopher	Wayne, PA 190871957	$500.00 6/6/2008
7	Cashman	Christopher	Lafayette Hill, PA 194441747	$500.00 6/6/2008
8	Chimicles	Christopher	Philadelphia, PA 191076063	$1,000.00 6/6/2008
9	Ryan	Dennis	Wayne, PA 19087	$500.00 6/10/2008
10	Mramor	Andrew	Havertown, PA 190835517	$100.00 6/6/2008
11	Storey	Bayard	Philadelphia, PA 191303202	$2,600.00 6/5/2008
12	Wolf	Bryan	Ardmore, PA 190032509	$500.00 6/10/2008
13	Adams	Carolyn	Philadelphia, PA 191442721	$1,000.00 6/6/2008
14	Tecosky	David	Philadelphia, PA 191521815	$100.00 6/6/2008
15	Stefanowicz	Elaine	Philadelphia, PA 191283208	$100.00 6/6/2008
16	Emmett	Gary	Philadelphia, PA 191036516	$100.00 6/5/2008
17	Fernandez	Happy	Philadelphia, PA 191042066	$1,000.00 6/6/2008
18	Bruno	Leonard	Huntingdon Valley, PA 190066357	$2,000.00 6/5/2008
19	Rossman	Louis	Bala Cynwyd, PA 190042335	$100.00 6/6/2008
20	Bell	Madeline	Penn Valley, PA 190721420	$500.00 6/10/2008
21	Fink	Paul	Merion Station, PA 190661717	$200.00 5/20/2008
22	Carroll	Peter	Willow Grove, PA 190904210	$100.00 6/6/2008
23	Squillaro	Richard	Philadelphia, PA 191454638	$100.00 6/6/2008
24	Markiewicz	Stanley	Blackwood,J 080125541	$100.00 6/6/2008
25	Altschuler	Steven	Philadelphia, PA 191035777	$1,000.00 6/12/2008
26	Gamba	Thomas	Philadelphia, PA 191036228	$200.00 6/6/2008
27	Kohler	Thomas	Philadelphia, PA 19103	$100.00 6/6/2008
2008-4

Then, I need to compare it to a list that looks like this:

Excel Workbook
	A	B	C	D	E	F	G
2	ADELBERG KENNETH	DEVON*	3/31/2009	2400	2009-2010	Sestak	Through Committee
3	ADELBERG KENNETH	DEVON*	3/25/2009	2400	2009-2010	Sestak	Through Committee
4	ALCHIN, JOHN R.	PHILADELPHIA*	3/14/2009	1000	2009-2010	Sestak	Individual
5	ALDERFER R. BRENT	WAYNE*	3/30/2009	500	2009-2010	Sestak	Through Committee
6	ANDERSON, HARRIET V.	NEWTOWN SQUARE*	3/25/2009	250	2009-2010	Sestak	Individual
7	ANDERSON, THOMAS J.	ARLINGTON*	3/30/2009	1000	2009-2010	Sestak	Individual
8	ARMAO, CHRISTOPHER S.	RESTON*	3/23/2009	500	2009-2010	Sestak	Individual
9	ATTERBURY ELIZABETH G.	MALVERN*	3/24/2009	500	2009-2010	Sestak	Through Committee
10	BABIN, JOHN H.	MEDIA*	3/27/2009	2400	2009-2010	Sestak	Individual
11	BABIN, MARYANN C.	MEDIA*	3/27/2009	2400	2009-2010	Sestak	Individual
12	BARUCH, ANN R.	HAVERFORD*	3/31/2009	2400	2009-2010	Sestak	Individual
13	BENOLIEL, PETER A	WAYNE*	3/25/2009	500	2009-2010	Sestak	Individual
14	BENTER WILLIAM	PITTSBURGH*	3/31/2009	1000	2009-2010	Sestak	Through Committee
15	BERGER, DANIEL	PHILADELPHIA*	3/31/2009	2400	2009-2010	Sestak	Individual
16	BERGER, DANIEL	PHILADELPHIA*	3/31/2009	2400	2009-2010	Sestak	Individual
17	BERGER, HARRIET	PHILADELPHIA*	3/31/2009	2400	2009-2010	Sestak	Individual
18	BERGER, HARRIET	PHILADELPHIA*	3/31/2009	2400	2009-2010	Sestak	Individual
19	BERNSTEIN CLAUDE D.F.	NEW CANAAN*	3/30/2009	500	2009-2010	Sestak	Through Committee
20	BEST WILLIAM J.	HUNTINGDON VALLEY*	3/28/2009	2400	2009-2010	Sestak	Through Committee
21	BEVAN JOHN S	VILLANOVA*	3/31/2009	500	2009-2010	Sestak	Through Committee
22	BEVAN, JOHN S	VILLANOVA*	3/10/2009	500	2009-2010	Sestak	Individual
23	BILLMERS RICHARD IRA	LANGHORNE*	3/29/2009	250	2009-2010	Sestak	Through Committee
24	BLUM, HOWARD A.	ELKINS PARK*	3/10/2009	500	2009-2010	Sestak	Individual
25	BOGLE, JOHN C.	VALLEY FORGE*	3/31/2009	2400	2009-2010	Sestak	Individual
26	BOULTER, BEAU	ARLINGTON*	3/23/2009	1000	2009-2010	Sestak	Individual
09-10

What i'm ultimately looking for is a list of the names (and the data in the columns next to each name) of all the people that appear on at least 3 lists like this.

Any ideas?

 

[Legacy 68668]

Don't understand about Col.F & G
try this anyway.
The result will be on Sheet2

Sub test()
Dim a, b(), i As Long, n As Long
a = Range("a1").CurrentRegion.Value
ReDim b(1 To UBound(a, 1) \ 3 + 1, 1 To 7)
For i = 1 To UBound(a, 1) Step 3
    n = n + 1
    b(n, 1) = Split(Split(a(i, 1), " ",2)(1), ",")(0)
    b(n, 2) = Split(a(i + 1, 1), ",")(0)
    b(n, 3) = Split(a(i + 2, 1))(1)
    b(n, 4) = Split(a(i + 2, 1))(0)
    b(n, 5) = Year(DateValue(b(n, 3))) & "-" & _
          Year(DateValue(b(n, 3))) + 1
    b(n, 6) = "Sestak"
Next
Sheets("sheet2").Cells(1).Resize(n, 7).Value = b
End Sub

 

[Jacophile]

If you only have to do it a few times I would suggest, instead of just Paste, trying Edit/Paste Special and select Text when you paste it into the workbook. This will format it it into columns as you require.

 

[mesolomo]

When I run that macro on the list like the first one, it tells me "subscript out of range."

It gives me the option to debug, but I have no idea what to do.

Let me know if you have any ideas!

Thanks!

 

[Legacy 68668]

Can you step through the code ?

When you are in VBE
1) Click somewhere on the code.
2) As you hit F8, the code will execute one line.

Can you find the line that gives you an error ?

 

[mesolomo]

Do I highlight a line in yellow (using f8) then go to the excel doc and run the macro?

I opened VBE and pressed F8 all the way down and nothing looked strange (although I don't know what to look for)

 

[Legacy 68668]

1) Click somewhere on the code.
2) As you hit F8, the code will execute one line.

Then hit F8 again and again until the code gives you an error.

 

[mesolomo]

It goes all the way down, then when it reaches the bottom it moves up to the first value that starts with "b" under the "For" heading, and just travels up and down that set. No error comes up.