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

Offline JosephM

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Jan 2014
  • Posts: 290
  • Total likes: 1
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #580 on: March 09, 2016, 03:46:32 AM »
when i do that the number does indeed flip to the right side of the box if that means anything, but I have hundreds of rows....

Right click the top of the column, there should be an option to format cells

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12697
  • Total likes: 7377
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #581 on: March 09, 2016, 09:34:07 AM »
Right click the top of the column, there should be an option to format cells
That will only work if you want to change the entire column
Feelings don't care about your facts

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12110
  • Total likes: 2164
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #582 on: March 09, 2016, 10:18:21 AM »
the fix:
Set the column to desired format and then select all the cells in that column. Go to Data-->Text To Columns and press finish. That should get them all in one swoop.
+1
Workflowy. You won't know what you're missing until you try it.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7273
  • Total likes: 288
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #583 on: March 09, 2016, 01:04:37 PM »
Or select all the affected cells and click the error in top one, convert to number. Will convert them all

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12697
  • Total likes: 7377
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #584 on: March 09, 2016, 01:05:49 PM »
Or select all the affected cells and click the error in top one, convert to number. Will convert them all
What if there is no error?
Feelings don't care about your facts

Offline Jkhein

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2010
  • Posts: 4538
  • Total likes: 13
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #585 on: March 09, 2016, 01:13:23 PM »
Or select all the affected cells and click the error in top one, convert to number. Will convert them all
Good, was wondering how to do that

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12110
  • Total likes: 2164
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #586 on: March 09, 2016, 03:20:13 PM »
Is there a format type that will show TRUE if the value is 1, and FALSE if it's 0?
Workflowy. You won't know what you're missing until you try it.

Online lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5154
  • Total likes: 574
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #587 on: March 09, 2016, 04:33:10 PM »
Is there a format type that will show TRUE if the value is 1, and FALSE if it's 0?
Don't know about format, but a simple formula should do the trick. If the 1 / 0 is the result of a formula, you should be able to nest this inside.

=A1>=1

ETA: The term you are looking for is "Boolean". Excel does not have an to format as Boolean, but you can generate it by formula (as above) or by VBA.
« Last Edit: March 09, 2016, 04:40:03 PM by lubaby »

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12110
  • Total likes: 2164
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #588 on: March 09, 2016, 05:23:20 PM »
Don't know about format, but a simple formula should do the trick. If the 1 / 0 is the result of a formula, you should be able to nest this inside.

=A1>=1

ETA: The term you are looking for is "Boolean". Excel does not have an to format as Boolean, but you can generate it by formula (as above) or by VBA.
Yeah, but then I need another column. In this situation it's probably easier to just change them manually (sort the column, select the 1s, type True and press control-enter...)

Thanks, though
Workflowy. You won't know what you're missing until you try it.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12110
  • Total likes: 2164
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #589 on: March 10, 2016, 03:23:44 PM »
Yeah, but then I need another column. In this situation it's probably easier to just change them manually (sort the column, select the 1s, type True and press control-enter...)

Thanks, though
I wrote a macro:
Code: [Select]
Sub TrueFalse()
    For Each cell In Selection
        If cell.Value = 0 Then
            cell.Value = "FALSE"
        Else
            cell.Value = "TRUE"
        End If
    Next
End Sub
Workflowy. You won't know what you're missing until you try it.

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7273
  • Total likes: 288
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #590 on: March 10, 2016, 04:11:24 PM »
I think it's just that one in the bottom left corner of the screenshot that matters.

I had a similar issue to your's once, except everything was formatted in euros. I ended up finding a script to essentially clear out all the styles and rebuild the standard style database - like this (found [ur=http://www.mrexcel.com/forum/excel-questions/390298-cell-styles-gone-wild-how-do-i-delete-them.htmll]here[/url]):
Code: [Select]
Sub RebuildDefaultStyles()

'The purpose of this macro is to remove all styles in the active
'workbook and rebuild the default styles.
'It rebuilds the default styles by merging them from a new workbook.

'Dimension variables.
   Dim MyBook As Workbook
   Dim tempBook As Workbook
   Dim CurStyle As Style

   'Set MyBook to the active workbook.
   Set MyBook = ActiveWorkbook
   On Error Resume Next
   'Delete all the styles in the workbook.
   For Each CurStyle In MyBook.Styles
      'If CurStyle.Name <> "Normal" Then CurStyle.Delete
      Select Case CurStyle.Name
         Case "20% - Accent1", "20% - Accent2", _
               "20% - Accent3", "20% - Accent4", "20% - Accent5", "20% - Accent6", _
               "40% - Accent1", "40% - Accent2", "40% - Accent3", "40% - Accent4", _
               "40% - Accent5", "40% - Accent6", "60% - Accent1", "60% - Accent2", _
               "60% - Accent3", "60% - Accent4", "60% - Accent5", "60% - Accent6", _
               "Accent1", "Accent2", "Accent3", "Accent4", "Accent5", "Accent6", _
               "Bad", "Calculation", "Check Cell", "Comma", "Comma [0]", "Currency", _
               "Currency [0]", "Explanatory Text", "Good", "Heading 1", "Heading 2", _
               "Heading 3", "Heading 4", "Input", "Linked Cell", "Neutral", "Normal", _
               "Note", "Output", "Percent", "Title", "Total", "Warning Text"
            'Do nothing, these are the default styles
         Case Else
            CurStyle.Delete
      End Select

   Next CurStyle

   'Open a new workbook.
   Set tempBook = Workbooks.Add

   'Disable alerts so you may merge changes to the Normal style
   'from the new workbook.
   Application.DisplayAlerts = False

   'Merge styles from the new workbook into the existing workbook.
   MyBook.Styles.Merge Workbook:=tempBook

   'Enable alerts.
   Application.DisplayAlerts = True

   'Close the new workbook.
   tempBook.Close

End Sub

ETA: This will only work if new workbooks open fine - with no style/format issues.
This worked great, thanks!

Workbooks are still very slow though. At least I can now control the formatting.

Offline Jkhein

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2010
  • Posts: 4538
  • Total likes: 13
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #591 on: March 14, 2016, 07:07:19 PM »
Is there any way to remove a duplicate word anytime there is one in a specific column? remove dulicates only works on dulpicate values, I want that there should not be any of the same word more then once.
Find and Replace would work if I manually go through each word but I'm wondering if there are any other ways to do this.

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12110
  • Total likes: 2164
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #592 on: March 14, 2016, 07:45:23 PM »
Is there any way to remove a duplicate word anytime there is one in a specific column? remove dulicates only works on dulpicate values, I want that there should not be any of the same word more then once.
Find and Replace would work if I manually go through each word but I'm wondering if there are any other ways to do this.
Do you meant the same word twice, within one cell?
Workflowy. You won't know what you're missing until you try it.

Offline Jkhein

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2010
  • Posts: 4538
  • Total likes: 13
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #593 on: March 14, 2016, 08:46:27 PM »
Do you meant the same word twice, within one cell?
I mean within a certain range - I can put it into one cell if needed to be able to do this

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7273
  • Total likes: 288
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #594 on: March 14, 2016, 08:51:22 PM »
Is there any way to remove a duplicate word anytime there is one in a specific column? remove dulicates only works on dulpicate values, I want that there should not be any of the same word more then once.
Find and Replace would work if I manually go through each word but I'm wondering if there are any other ways to do this.
Text is a value also. Not sure why remove duplicates is not working?

You can make a rule and then sort by it or something..

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12110
  • Total likes: 2164
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #595 on: March 14, 2016, 08:57:58 PM »
I mean within a certain range - I can put it into one cell if needed to be able to do this
I'm not sure I understand your situation. Can you post a small sample of your data?
Workflowy. You won't know what you're missing until you try it.

Offline yitzf

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2655
  • Total likes: 51
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #596 on: March 14, 2016, 09:17:17 PM »
Is there any way to remove a duplicate word anytime there is one in a specific column? remove dulicates only works on dulpicate values, I want that there should not be any of the same word more then once.
Find and Replace would work if I manually go through each word but I'm wondering if there are any other ways to do this.

Paste this into a vba module (Alt+F11 then right click on your book in the left pane, and insert->module)
Code: [Select]
Public Function GetUnique(rng As Range, Optional Outputseparator As String = " ") As String
'*******************
'Helped by
'http://stackoverflow.com/questions/15144816/batch-string-concatenation-in-excel
'http://www.mrexcel.com/forum/excel-questions/740936-how-find-duplicate-word-cell.html
'*******************

Dim arr As Variant
Dim myDic As Object
Dim outDic As Object
Dim L As Long
Dim concattedList As String
Dim rangeCell As Range
Dim rangeText As String
Dim delimiter As String

    delimiter = " "
    concattedList = ""

  For Each rangeCell In rng.Cells

    rangeText = rangeCell.Value
   
    If Len(rangeText) > 0 Then
     
      If (Len(concattedList) > 0) Then
        concattedList = concattedList & delimiter & rangeText
      Else
        concattedList = rangeText
      End If
    End If

  Next rangeCell

arr = Split(concattedList)
If UBound(arr) > 0 Then
    Set myDic = CreateObject("Scripting.Dictionary")
    Set outDic = CreateObject("Scripting.Dictionary")
    For L = LBound(arr) To UBound(arr)
        If Not myDic.exists(arr(L)) Then
            myDic(arr(L)) = 0
        Else:
            outDic(arr(L)) = 0
        End If
    Next
    GetUnique = Join(myDic.Keys, Outputseparator)
End If
End Function

Then in your worksheet type =GetUnique(A1:A200)
(substitute A1:A200 with your range) It works like any other function in excel (i.e. sum or vlookup)

This will give you a list with spaces in between each word. If you want another delimiter - after the range type ,";" (where ; is the delimiter) for example GetUnique(A1:A200,";").

Offline Jkhein

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Oct 2010
  • Posts: 4538
  • Total likes: 13
  • DansDeals.com Hat Tips 4
    • View Profile
Re: Excel Problem
« Reply #597 on: March 14, 2016, 11:33:38 PM »
I'm not sure I understand your situation. Can you post a small sample of your data?
Text is a value also. Not sure why remove duplicates is not working?

You can make a rule and then sort by it or something..
I have a column with 1000 keywords, for eg. cell 1, tasty fish, cell 2, tasty meat, cell 3, tasty chicken, cell 4, good fruit, cell 5, good vegetables, and so on, I want to remove all the duplicated like "tasty" and "good", only keep the unique words.
Remove duplicates only removes if there are duplicate cell values like twice "tasty meat" or twice "tasty chicken"
Paste this into a vba module
will try soon thanks

Offline etech0

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: Dec 2013
  • Posts: 12110
  • Total likes: 2164
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Problem
« Reply #598 on: March 14, 2016, 11:43:22 PM »
I have a column with 1000 keywords, for eg. cell 1, tasty fish, cell 2, tasty meat, cell 3, tasty chicken, cell 4, good fruit, cell 5, good vegetables, and so on, I want to remove all the duplicated like "tasty" and "good", only keep the unique words.
Remove duplicates only removes if there are duplicate cell values like twice "tasty meat" or twice "tasty chicken"will try soon thanks
What if there was "tasty fish" and "good fish"? Would you want to remove both instances of the word "fish"? (Or does that not come up anyways?)
Workflowy. You won't know what you're missing until you try it.

Online aygart

  • Dansdeals Lifetime 10K Presidential Platinum Elite
  • *******
  • Join Date: May 2008
  • Posts: 12697
  • Total likes: 7377
  • DansDeals.com Hat Tips 11
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Problem
« Reply #599 on: March 14, 2016, 11:56:12 PM »
I have a column with 1000 keywords, for eg. cell 1, tasty fish, cell 2, tasty meat, cell 3, tasty chicken, cell 4, good fruit, cell 5, good vegetables, and so on, I want to remove all the duplicated like "tasty" and "good", only keep the unique words.
Remove duplicates only removes if there are duplicate cell values like twice "tasty meat" or twice "tasty chicken"will try soon thanks
how many unique duplicate words are there
Feelings don't care about your facts