Topic Wiki

Rule 1: Did you try A.I? Right before you click post, give it a quick run. Most excel questions can be answered very quickly, thoroughly, and clearly via chatgpt. Wrong answer, or having trouble with the correct wording of the prompt? Post for help

=================
For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by Jojo202 on March 01, 2024, 12:09:44 AM »

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

Offline stooges44

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2017
  • Posts: 6453
  • Total likes: 2746
  • DansDeals.com Hat Tips 269
    • 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.

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • 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 Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2017
  • Posts: 6453
  • Total likes: 2746
  • DansDeals.com Hat Tips 269
    • 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.

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • 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 Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jan 2017
  • Posts: 6453
  • Total likes: 2746
  • DansDeals.com Hat Tips 269
    • 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.

Offline Live N Learn

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2015
  • Posts: 3799
  • Total likes: 332
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1125 on: January 24, 2018, 11:10:06 PM »
Is it possible to have the "number format" of a cell depend on a =IF?
".איזהו חכם, הלומד מכל אדם"

Offline aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 17396
  • Total likes: 14333
  • DansDeals.com Hat Tips 14
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1126 on: January 24, 2018, 11:56:50 PM »
Is it possible to have the "number format" of a cell depend on a =IF?
Conditional formatting
Feelings don't care about your facts

Offline Live N Learn

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2015
  • Posts: 3799
  • Total likes: 332
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1127 on: January 25, 2018, 12:14:25 AM »
Conditional formatting
I tried... I'm looking to do, if A1="1" it's a Percentage, and if A1="2" then it's a Fraction.
« Last Edit: January 25, 2018, 12:17:30 AM by User6669 »
".איזהו חכם, הלומד מכל אדם"

Online skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3810
  • Total likes: 826
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1128 on: January 25, 2018, 01:40:36 AM »
I tried... I'm looking to do, if A1="1" it's a Percentage, and if A1="2" then it's a Fraction.
Has to be 2 separate rules. One with =a1="1" as the formula and formatted as a percentage, and the other with =a1="2" as the formula and formatted as a fraction.

The conditions can be tricky though. If you can't get it to work, can post what the conditions are and I can't try to create the exact formula.

Offline Live N Learn

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2015
  • Posts: 3799
  • Total likes: 332
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1129 on: January 25, 2018, 10:28:43 PM »
Has to be 2 separate rules. One with =a1="1" as the formula and formatted as a percentage, and the other with =a1="2" as the formula and formatted as a fraction.

The conditions can be tricky though. If you can't get it to work, can post what the conditions are and I can't try to create the exact formula.

It needs to check the cell to the right, and format it based on what is says there. E.g. for B4 it should check B5, C4 = C5 etc.

 The condition is, =$B5="Fraction".

The cell value is correct, it just doesn't display the correct format.
".איזהו חכם, הלומד מכל אדם"

Online skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3810
  • Total likes: 826
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1130 on: January 25, 2018, 11:56:53 PM »
It needs to check the cell to the right, and format it based on what is says there. E.g. for B4 it should check B5, C4 = C5 etc.

 The condition is, =$B5="Fraction".

The cell value is correct, it just doesn't display the correct format.
Are you saying you got it to work? Or you're describing what you did and it's still not working?

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1131 on: January 26, 2018, 12:14:26 AM »
You can use another column with a function referring to the previous 2 columns.

Like this:
Code: [Select]
=IF(A1="fraction",TEXT(B1,"# ?/?"),TEXT(B1,"0%"))
Where A is the column that  says "fraction" or "percent", and B is the column with the number in it.

This link will give you more instructions in case you want to modify the number formats (ie more decimal places etc) https://support.office.com/en-us/article/TEXT-function-20d5ac4d-7b94-49fd-bb38-93d29371225c
« Last Edit: January 26, 2018, 12:37:30 AM by etech0 »
Workflowy. You won't know what you're missing until you try it.

Offline jose34

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2016
  • Posts: 2396
  • Total likes: 154
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Help and Problems
« Reply #1132 on: February 11, 2018, 05:22:05 PM »
Someone asked me to find all the people that attend events over the course of the year and how many events, they just need numbers not the people names.
Is their anyway to put it the data in the Excel and have excel do the work for me?


Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5335
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Help and Problems
« Reply #1133 on: February 11, 2018, 05:26:28 PM »
Someone asked me to find all the people that attend events over the course of the year and how many events, they just need numbers not the people names.
Is their anyway to put it the data in the Excel and have excel do the work for me?
Sure, wanna post some sample data so we can take a look? Should be a pretty simple formula..

Offline jose34

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Aug 2016
  • Posts: 2396
  • Total likes: 154
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Help and Problems
« Reply #1134 on: February 11, 2018, 05:33:52 PM »
                        
Something like in this in a Excel file, This sample is only four names per column put I am dealing with like 20 events of 20- 100 people each.


   5-Apr         6-May         7-Jun   
                        
   Jack    Mitchel      Yehuda    Haim      Avi   Yakubov
   Kathrine    Katz      Lary    Yeuda      Chevy   Katz
   Ron    Mathew      Mitch    Paul      Paul   Loe
   Yehuda    Haim      Pat    King      Mitch   Paul

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7355
  • Total likes: 301
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1135 on: February 11, 2018, 06:30:17 PM »

Something like in this in a Excel file, This sample is only four names per column put I am dealing with like 20 events of 20- 100 people each.


5-Apr6-May7-Jun

Jack MitchelYehuda HaimAviYakubov
Kathrine KatzLary YeudaChevyKatz
Ron MathewMitch PaulPaulLoe
Yehuda HaimPat KingMitchPaul
First make a unique list per event by copying and  removing dup's, then Countif(a2:a100,"jack") ?

Offline Centurion

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2012
  • Posts: 1306
  • Total likes: 5
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York / Jerusalem
  • Programs: spg plat,avis pres ,hertz plat ,AAexcutime, delta plat,hilton gold, united
Re: Excel Help and Problems
« Reply #1136 on: February 20, 2018, 05:35:26 PM »
having a hard time copying hyperlinks from one book to another
it looks good ( blue and underlined) but the only way to create hyperlink is by hitting enter twice on each line ( hundreds of lines)
https://www.americanexpress.com/ ( it creates a link here but in excel it doesnt)
first its black then i can make it blue and underline.

thank you
you can only make a first impression ONCE

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7355
  • Total likes: 301
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1137 on: February 20, 2018, 06:04:32 PM »
having a hard time copying hyperlinks from one book to another
it looks good ( blue and underlined) but the only way to create hyperlink is by hitting enter twice on each line ( hundreds of lines)
https://www.americanexpress.com/ ( it creates a link here but in excel it doesnt)
first its black then i can make it blue and underline.

thank you
You need to refresh all cells in the column. (same as double clicking each).

To do this select the column and do a text to column function. Set the the delimitor as nothing so it won't actually shift. Just refresh in place.

Offline MasterAmex

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Dec 2015
  • Posts: 300
  • Total likes: 5
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1138 on: February 21, 2018, 03:00:31 PM »
is there a separate thread for Google sheets, or its a "all in one"?

Online etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12861
  • Total likes: 3316
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1139 on: February 21, 2018, 03:10:03 PM »
is there a separate thread for Google sheets, or its a "all in one"?
Google Sheets help
Workflowy. You won't know what you're missing until you try it.