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 287238 times)

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 Problem
« Reply #120 on: May 22, 2014, 12:01:11 AM »
Do I have to do the formula in column b. Why can't I set this formula in a? (Sounds like a question of an illiterate,I know)
You can do it in any column you want. =proper(FIELD YOU WANT CHANGED)

The formula results (fixed) depend on the data that you are referencing. If you want to remove the original (bad) column, you need to first copy the column of fixed data and 'paste special' it as text only (that's under 'paste other' as the 123). Otherwise if you delete the original name column the fixed formula data will disappear too.
Or just leave it in.
« Last Edit: May 22, 2014, 12:05:06 AM by lubaby »

Offline MarkS

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2009
  • Posts: 3761
  • Total likes: 70
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #121 on: May 22, 2014, 12:01:19 AM »
Do I have to do the formula in column b. Why can't I set this formula in a? (Sounds like a question of an illiterate,I know)
Where is your data? If it's in Column A, put this formula in column B. If you already have something else in column B, add a column between A and B. Once you've put in the formula and have everything as you want it in Column B, copy the column, right click and hit paste special and select paste values. At that point you can delete column A and the corrected data will move over to column A.

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 Problem
« Reply #122 on: May 22, 2014, 12:03:24 AM »
Do I have to do the formula in column b. Why can't I set this formula in a? (Sounds like a question of an illiterate,I know)
You can't operate a formula in the cell that you are referring to in your formula.

Offline unavailable

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Dec 2013
  • Posts: 545
  • Total likes: 20
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Problem
« Reply #123 on: May 22, 2014, 03:27:39 PM »
well thanks to all ddf'ers
i opted for the most simple way, =proper(a1) then dragged down, copied and pasted values, it worked!

Offline unavailable

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Dec 2013
  • Posts: 545
  • Total likes: 20
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Problem
« Reply #124 on: May 22, 2014, 03:30:06 PM »
is there a way to remove the titles; Mr&Mrs, Rabbi&Mrs ETC. they all came jumbled in one column, how can I copy then to separate column?
tia

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 Problem
« Reply #125 on: May 22, 2014, 03:33:45 PM »
is there a way to remove the titles; Mr&Mrs, Rabbi&Mrs ETC. they all came jumbled in one column, how can I copy then to separate column?
tia
Click Data, text to columns, play with it.

Offline 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 Problem
« Reply #126 on: May 22, 2014, 03:35:29 PM »
is there a way to remove the titles; Mr&Mrs, Rabbi&Mrs ETC. they all came jumbled in one column, how can I copy then to separate column?
tia
easiest way:
Click Data ribbon
Select the column with the data
Click Text to Columns, and use & as the delimiter.
Workflowy. You won't know what you're missing until you try it.

Offline MarkS

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2009
  • Posts: 3761
  • Total likes: 70
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #127 on: May 22, 2014, 03:40:52 PM »
is there a way to remove the titles; Mr&Mrs, Rabbi&Mrs ETC. they all came jumbled in one column, how can I copy then to separate column?
tia
Use text to columns
If all columns start with a title, do text to column using & and space as your delimiters. 
If you want to then make 1 column showing Mr & Mrs, you can the do a concatenate using     =concatenate(A1," ","&"," ", B1)

 
« Last Edit: May 22, 2014, 03:47:44 PM by MarkS »

Offline unavailable

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Dec 2013
  • Posts: 545
  • Total likes: 20
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Problem
« Reply #128 on: May 22, 2014, 06:32:56 PM »
Thanks it's really a help

Offline bubbles

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2011
  • Posts: 5060
  • Total likes: 20
  • DansDeals.com Hat Tips 67
    • View Profile
Re: Excel Problem
« Reply #129 on: May 22, 2014, 06:41:15 PM »
Use text to columns

If you want to then make 1 column showing Mr & Mrs, you can the do a concatenate using     =concatenate(A1," & ", B1)

FTFY  ;)

Offline 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 Problem
« Reply #130 on: May 22, 2014, 08:53:38 PM »
FTFY  ;)
I always look for smallest number of characters in the formula:

=A1&" & "&B1

Offline EJB

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Sep 2012
  • Posts: 5409
  • Total likes: 254
  • DansDeals.com Hat Tips 15
    • View Profile
Re: Excel Problem
« Reply #131 on: May 22, 2014, 09:03:05 PM »
Maybe it's too simple ;)

Yup. You learn something every day.

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 Problem
« Reply #132 on: May 22, 2014, 09:08:59 PM »
I always look for smallest number of characters in the formula:

=A1&" & "&B1
+1
Never saw the point to 'concatenate' anything, since the =A1&B1 works great.
Is it something new that didn't exist in earlier excels?

Offline 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 Problem
« Reply #133 on: May 22, 2014, 09:22:21 PM »
+1
Never saw the point to 'concatenate' anything, since the =A1&B1 works great.
Is it something new that didn't exist in earlier excels?
I've seen some discussion as to which method is more efficient, but no one knows for sure. CONCATENATE is most likely the newer of the 2 methods, as & exists as an operator in many programming languages that predate Excel.

Offline MarkS

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2009
  • Posts: 3761
  • Total likes: 70
  • DansDeals.com Hat Tips 5
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #134 on: May 22, 2014, 09:36:38 PM »
+1
Never saw the point to 'concatenate' anything, since the =A1&B1 works great.
Is it something new that didn't exist in earlier excels?
Sometimes =A1&B1 doesnt work (possibly when the cells are formatted diferently) and I've found concatenate to work mire consistently

Offline smurf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2010
  • Posts: 4525
  • Total likes: 276
  • DansDeals.com Hat Tips 8
    • View Profile
  • Location: NJ
Re: Excel Problem
« Reply #135 on: May 22, 2014, 10:36:59 PM »
Sometimes =A1&B1 doesnt work (possibly when the cells are formatted diferently) and I've found concatenate to work mire consistently
and it's easier to combine more then 2 cells that way.
Regardless I don't like the way either method handles blank cells so I use a borrowed UDF

Offline 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 Problem
« Reply #136 on: May 23, 2014, 08:51:15 AM »
and it's easier to combine more then 2 cells that way.
Regardless I don't like the way either method handles blank cells so I use a borrowed UDF
Disagree on the first point. In what way is it easier? You have to put a comma in between each reference in CONCATENATE anyway, just sub in an ampersand and drop the CONCATENATE function.

And what's the issue with blank cells? A blank cell has the value "", which is what it puts in the cell value when you use either method. UDF's are always a last resort for me. They're notoriously inefficient relative to built in functions.

Offline smurf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Sep 2010
  • Posts: 4525
  • Total likes: 276
  • DansDeals.com Hat Tips 8
    • View Profile
  • Location: NJ
Re: Excel Problem
« Reply #137 on: May 23, 2014, 12:51:32 PM »
Disagree on the first point. In what way is it easier? You have to put a comma in between each reference in CONCATENATE anyway, just sub in an ampersand and drop the CONCATENATE function.

And what's the issue with blank cells? A blank cell has the value "", which is what it puts in the cell value when you use either method. UDF's are always a last resort for me. They're notoriously inefficient relative to built in functions.
you're right,I forgot how to use concatenate. I haven't used it in a while.

My issue with blank cells is that i often need to concatenate a range of cells with a delimiter between each one. The simple way to do it is to store the delimiter in a cell and reference it between each cell in the concatenate. However with a blank cell I'll then have 2 consecutive delimiter.
So I use udf

Offline unavailable

  • Dansdeals Platinum Elite + Lifetime Silver Elite
  • *****
  • Join Date: Dec 2013
  • Posts: 545
  • Total likes: 20
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: bp
Re: Excel Problem
« Reply #138 on: July 09, 2014, 10:26:49 PM »
hi
when removing duplicates is there an option that it should ask you what duplicate to remove/keep. or at least show what duplicate was removed?

tia

Offline bubbles

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2011
  • Posts: 5060
  • Total likes: 20
  • DansDeals.com Hat Tips 67
    • View Profile
Re: Excel Problem
« Reply #139 on: July 09, 2014, 10:51:52 PM »
hi
when removing duplicates is there an option that it should ask you what duplicate to remove/keep. or at least show what duplicate was removed?

tia

if they are duplicates what's the difference? you can highlight duplicates instead of deleting right away under condition formatting > highlight cells rules > duplicate values.

If you let us know what you are trying to accomplish maybe can be of more help