Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

Author Topic: Excel Help and Problems  (Read 59802 times)

Online ual902

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Apr 2013
  • Posts: 1422
  • Total likes: 5
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1110 on: December 01, 2017, 12:51:46 PM »
Select row 7 (by clicking on the "7"), right click on it, click Insert Row. Or select row 7 and type "control shift +"

Thanks that worked.

Offline yoruel

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: May 2013
  • Posts: 657
  • Total likes: 1
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York
Re: Excel Help and Problems
« Reply #1111 on: January 01, 2018, 09:40:06 PM »
Is there  way to use text to column and then have all the same data go to same column?
 ex. In a1, mom,dad,son,last name
In b1 son, mom, last name
C1 mom, last name.
D1, mom,son,dad,brother, sister,aunt,last name.

How do I get all the unique values into the same column?

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9024
  • Total likes: 224
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1112 on: January 01, 2018, 10:36:33 PM »
Is there  way to use text to column and then have all the same data go to same column?
 ex. In a1, mom,dad,son,last name
In b1 son, mom, last name
C1 mom, last name.
D1, mom,son,dad,brother, sister,aunt,last name.

How do I get all the unique values into the same column?
So in the output, you want the "mom" column to be empty if that row didn't have "mom" in it?
Workflowy. You won't know what you're missing until you try it.

Offline yoruel

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: May 2013
  • Posts: 657
  • Total likes: 1
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York
Re: Excel Help and Problems
« Reply #1113 on: January 02, 2018, 08:15:34 AM »
So in the output, you want the "mom" column to be empty if that row didn't have "mom" in it?
Yes, but i want the children in their own row.
Would index match work, after text to column?

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9024
  • Total likes: 224
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1114 on: January 02, 2018, 08:41:27 AM »
Yes, but i want the children in their own row.
Would index match work, after text to column?
I'm thinking you could do this with formulas but I'll need to work it out. Will each column be for a specific child's name, or just any child in general?
Workflowy. You won't know what you're missing until you try it.

Offline yoruel

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: May 2013
  • Posts: 657
  • Total likes: 1
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York
Re: Excel Help and Problems
« Reply #1115 on: January 02, 2018, 12:23:55 PM »
I'm thinking you could do this with formulas but I'll need to work it out. Will each column be for a specific child's name, or just any child in general?
Specific child's name.

Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 3988
  • Total likes: 124
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
Re: Excel Help and Problems
« Reply #1116 on: January 02, 2018, 12:32:32 PM »
Specific child's name.
Can you post a sample workbook? Or even screenshots of how you have it before, and how you want it to look after (by doing some manually)?
Once the game is over, the king and the pawn go back in the same box.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9024
  • Total likes: 224
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1117 on: January 02, 2018, 12:56:25 PM »
Specific child's name.
Perfect!
So keep column A as is, but insert a blank row at the top. In row 1, in columns B, C, etc, put the text that you want to show up in those columns

In B2 put this:
=IF(IFERROR(SEARCH(B$1,$A2),"")="","",B$1)

And copy it to the rest of the workbook.

Sample file attached
Workflowy. You won't know what you're missing until you try it.

Offline stooges44

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jan 2017
  • Posts: 4168
  • Total likes: 304
  • DansDeals.com Hat Tips 226
    • View Profile
Re: Excel Help and Problems
« Reply #1118 on: January 18, 2018, 12:36:02 PM »
Any "fast" ways to clean up a list of names and addresses?

The goal is to get each item in its own column (First, last, street, city, st, zip etcc..) and right now it's a total mess.

Currently the address is one big column which wouldn't be so terrible because I could use "text to columns" but there are also phone numbers (sometimes more then one) randomly thrown in, some before the address and some after and still some have notes like "corner of" so no matter how I cut it there would still be an enormous amount of manual editing.

I have 1540 rows like this  :(

TIA
« Last Edit: January 18, 2018, 12:39:18 PM by stooges44 »
If it's not free shipping it's not worth it.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9024
  • Total likes: 224
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1119 on: January 18, 2018, 12:39:11 PM »
Any "fast" ways to clean up a list of names and addresses?

The goal is to get each item in its own column (First, last, street, city, st, zip etcc..) and right now it's a total mess.

Currently the address is one big column which wouldn't be so terrible because I could use "text to columns" but there are also phone numbers (sometimes more then one) randomly thrown in, some before the address and some after and still some have notes like "corner of" so now matter how I cut it there would still be an enormous amount of manual editing.

I have 1540 rows like this  :(

TIA
Are you at all familiar with regular expressions? That may be your best bet.
Workflowy. You won't know what you're missing until you try it.

Offline stooges44

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jan 2017
  • Posts: 4168
  • Total likes: 304
  • DansDeals.com Hat Tips 226
    • View Profile
Re: Excel Help and Problems
« Reply #1120 on: January 18, 2018, 12:39:55 PM »
Are you at all familiar with regular expressions? That may be your best bet.

A few crumbs from college but that's about it
If it's not free shipping it's not worth it.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9024
  • Total likes: 224
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1121 on: January 18, 2018, 12:42:17 PM »
A few crumbs from college but that's about it
If you paste a few sample rows into rubular.com you'll be able to play around and see if you can get something good going. Excel doesn't have regex built in but there are addons for it, or you can just use google sheets. Once you have a base regex you can paste it into a column to run on all rows, then if any don't work you can tweak as needed.

If you want to post a few sample rows, I'll try and put something together for you
Workflowy. You won't know what you're missing until you try it.

Offline stooges44

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jan 2017
  • Posts: 4168
  • Total likes: 304
  • DansDeals.com Hat Tips 226
    • View Profile
Re: Excel Help and Problems
« Reply #1122 on: January 18, 2018, 12:53:41 PM »
If you paste a few sample rows into rubular.com you'll be able to play around and see if you can get something good going. Excel doesn't have regex built in but there are addons for it, or you can just use google sheets. Once you have a base regex you can paste it into a column to run on all rows, then if any don't work you can tweak as needed.

If you want to post a few sample rows, I'll try and put something together for you

Thanks!

Here's a sample of some of the worst: (I've replace the real data with nonsense)

Code: [Select]
77 old country rd new york ny 10001 H 9876542581 father dan 9876543210
9876 dans deals way ddf ny 115812 WC (111) 598-3254  - here the zip is intentionally too long bec that's how i have it
123 anywhere pl cleavland oh 11225 HC 1234567890 W 1234567891

As you can see there really is no pattern, some numbers have the (), some don't.
« Last Edit: January 18, 2018, 12:56:59 PM by stooges44 »
If it's not free shipping it's not worth it.

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9024
  • Total likes: 224
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1123 on: January 18, 2018, 01:46:36 PM »
Thanks!

Here's a sample of some of the worst: (I've replace the real data with nonsense)

Code: [Select]
77 old country rd new york ny 10001 H 9876542581 father dan 9876543210
9876 dans deals way ddf ny 115812 WC (111) 598-3254  - here the zip is intentionally too long bec that's how i have it
123 anywhere pl cleavland oh 11225 HC 1234567890 W 1234567891

As you can see there really is no pattern, some numbers have the (), some don't.
So this might be a start:

Put your data into column A of a google spreadsheet
Put this into column B and copy it down:

Code: [Select]
=regexextract(A1,"(?i)([\d\w ]* (rd|way|pl)) ([\w ]*) (AL|AK|AZ|AR|CA|CO|CT|DE|FL|GA|HI|ID|IL|IN|IA|KS|KY|LA|ME|MD|MA|MI|MN|MS|MO|MT|NE|NV|NH|NJ|NM|NY|NC|ND|OH|OK|OR|PA|RI|SC|SD|TN|TX|UT|VT|VA|WA|WV|WI|WY) (\d*) ([\w ]*) ([\d --()]*) (\D*) ([\d --()]*)")
The problem is that you'll have to manually scan through all  the rows to make sure nothing is missing, once you do that, it might just pay to do it manually.

But maybe give it a try, see if any rows return errors or are missing data, and we can tweak it as needed.

Also - this section: (rd|way|pl) will need to be modified to include all the options that your data includes for road/street/drive etc. It's not case sensitive at least :)
Workflowy. You won't know what you're missing until you try it.

Offline stooges44

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jan 2017
  • Posts: 4168
  • Total likes: 304
  • DansDeals.com Hat Tips 226
    • View Profile
Re: Excel Help and Problems
« Reply #1124 on: January 18, 2018, 01:53:13 PM »
So this might be a start:

Put your data into column A of a google spreadsheet
Put this into column B and copy it down:

Code: [Select]
=regexextract(A1,"(?i)([\d\w ]* (rd|way|pl)) ([\w ]*) (AL|AK|AZ|AR|CA|CO|CT|DE|FL|GA|HI|ID|IL|IN|IA|KS|KY|LA|ME|MD|MA|MI|MN|MS|MO|MT|NE|NV|NH|NJ|NM|NY|NC|ND|OH|OK|OR|PA|RI|SC|SD|TN|TX|UT|VT|VA|WA|WV|WI|WY) (\d*) ([\w ]*) ([\d --()]*) (\D*) ([\d --()]*)")
The problem is that you'll have to manually scan through all  the rows to make sure nothing is missing, once you do that, it might just pay to do it manually.

But maybe give it a try, see if any rows return errors or are missing data, and we can tweak it as needed.

Also - this section: (rd|way|pl) will need to be modified to include all the options that your data includes for road/street/drive etc. It's not case sensitive at least :)

Incredible! Thanks so much! I'll let you know how it goes
If it's not free shipping it's not worth it.