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

Offline AnonymousUser

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Feb 2013
  • Posts: 3001
  • Total likes: 13
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #100 on: May 19, 2014, 09:38:15 AM »
Whats an easy way to remove every second row of data?
Write a macro? And start from the bottom up.

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 #101 on: May 19, 2014, 09:42:32 AM »
Whats an easy way to remove every second row of data?
One way you can do it is:
In the column to the right of all the data, enter a 1 on the first row, 0 on 2nd row, drag it down to the bottom and select copy cells. Now, every other row will have a 1 at the end and every other row will have a 0.

Filter to show only the 1's and not the zeros.  Copy/paste the filtered data to a new tab and you'll have just the data you want.

Offline myb821

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Mar 2011
  • Posts: 7570
  • Total likes: 28
  • DansDeals.com Hat Tips 2
    • View Profile
  • Location: New York
Re: Excel Problem
« Reply #102 on: May 19, 2014, 09:44:59 AM »
Whats an easy way to remove every second row of data?
add a new column at the end of all your data. in the first row put a 1 and in the second row put a 2 drag down those two rows to the end of your data. Filter to only see the "2" rows. Then delete

Offline myb821

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Mar 2011
  • Posts: 7570
  • Total likes: 28
  • DansDeals.com Hat Tips 2
    • View Profile
  • Location: New York
Re: Excel Problem
« Reply #103 on: May 19, 2014, 09:45:25 AM »
One way you can do it is:
In the column to the right of all the data, enter a 1 on the first row, 0 on 2nd row, drag it down to the bottom and select copy cells. Now, every other row will have a 1 at the end and every other row will have a 0.

Filter to show only the 1's and not the zeros.  Copy/paste the filtered data to a new tab and you'll have just the data you want.
add a new column at the end of all your data. in the first row put a 1 and in the second row put a 2 drag down those two rows to the end of your data. Filter to only see the "2" rows. Then delete
you beat me to it

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 #104 on: May 19, 2014, 09:47:19 AM »
Was thinking something similar...
Thanks!

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 #105 on: May 19, 2014, 09:50:38 AM »
add a new column at the end of all your data. in the first row put a 1 and in the second row put a 2 drag down those two rows to the end of your data. Filter to only see the "2" rows. Then delete
How do I delete the "2" rows?

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 #106 on: May 19, 2014, 09:52:16 AM »
How do I delete the "2" rows?
Once they're filtered out, copy paste the data to another tab.

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 #107 on: May 19, 2014, 09:52:56 AM »
did "go to select", visible only and then deleted...

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 #108 on: May 19, 2014, 09:55:23 AM »
Once they're filtered out, copy paste the data to another tab.
That works also. another tab is key...

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 Problem
« Reply #109 on: May 19, 2014, 10:44:53 AM »
Or you can then sort by column a, and delete the whole lot of them.
Workflowy. You won't know what you're missing until you try it.

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 #110 on: May 21, 2014, 09:23:02 PM »
Is there a way to make the first letter of every word capital. I have a list of names (first and last) some are fully lower case done fully upper case, is there an option to get them all first letter capital and the rest lower case
TIA

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 #111 on: May 21, 2014, 09:24:25 PM »
Is there a way to make the first letter of every word capital. I have a list of names (first and last) some are fully lower case done fully upper case, is there an option to get them all first letter capital and the rest lower case
TIA

Use the function =proper()
If Column A has the text, in B1 type  =proper(A1) and drag that down

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 #112 on: May 21, 2014, 10:13:34 PM »
Use the function =proper()
If Column A has the text, in B1 type  =proper(A1) and drag that down
Thanks I'm not by a computer so I'll try later
Thanks again

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 #113 on: May 21, 2014, 11:23:52 PM »
Is there a way to make the first letter of every word capital. I have a list of names (first and last) some are fully lower case done fully upper case, is there an option to get them all first letter capital and the rest lower case
TIA
Use this Macro to trim (remove extra spaces) And make proper

Code: [Select]
Sub proper_trim()

Dim CleanTrimRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction

Set Func = Application.WorksheetFunction

On Error Resume Next
Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, xlTextValues)
If Err Then MsgBox "No data to clean and Trim!": Exit Sub

For Each oCell In CleanTrimRg
    oCell = Func.Proper(Func.Trim(oCell))
Next

End Sub

Then select the cells you want and run the macro.
You can easily adapt the code to only make proper or only trim if you like.
I know this can be done with a formula as others have mentioned but this way you dont have to add and remove columns

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 #114 on: May 21, 2014, 11:37:54 PM »
Thanks Smurf but it sounds to me like Chinese

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 #115 on: May 21, 2014, 11:45:28 PM »
Thanks Smurf but it sounds to me like Chinese

Here's a way without using VBA.

Assuming your first name is in cell A1, use the following code:
=UPPER(LEFT((A1),1))&LOWER(MID(A1,2,LEN(A1)-1))

If you want to add last name as well, you can quickly adjust the formula to do so.

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 #116 on: May 21, 2014, 11:48:53 PM »
Thanks Ejb this way does seem a bit easier but wouldn't be sure I got it until I tried it when I'll be on computer

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 #117 on: May 21, 2014, 11:50:33 PM »
Here's a way without using VBA.

Assuming your first name is in cell A1, use the following code:
=UPPER(LEFT((A1),1))&LOWER(MID(A1,2,LEN(A1)-1))

If you want to add last name as well, you can quickly adjust the formula to do so.
Once going with a formula why not just use =Proper()?

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 #118 on: May 21, 2014, 11:57:32 PM »
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)

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 #119 on: May 21, 2014, 11:59:08 PM »
Once going with a formula why not just use =Proper()?
Maybe it's too simple ;)