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

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7106
  • Total likes: 252
  • 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: 3706
  • Total likes: 11
  • 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: 7106
  • Total likes: 252
  • 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: 7106
  • Total likes: 252
  • 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...

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 9228
  • Total likes: 279
  • 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
  • ****
  • Join Date: Dec 2013
  • Posts: 470
  • Total likes: 7
  • 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: 3706
  • Total likes: 11
  • 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
  • ****
  • Join Date: Dec 2013
  • Posts: 470
  • Total likes: 7
  • 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: 4394
  • Total likes: 1
  • 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
  • ****
  • Join Date: Dec 2013
  • Posts: 470
  • Total likes: 7
  • 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 Presidential Platinum Elite
  • ********
  • Join Date: Sep 2012
  • Posts: 4845
  • Total likes: 129
  • DansDeals.com Hat Tips 14
    • 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
  • ****
  • Join Date: Dec 2013
  • Posts: 470
  • Total likes: 7
  • 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: 4394
  • Total likes: 1
  • 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
  • ****
  • Join Date: Dec 2013
  • Posts: 470
  • Total likes: 7
  • 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 Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 4364
  • Total likes: 192
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
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 ;)
Once the game is over, the king and the pawn go back in the same box.