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

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 #600 on: March 15, 2016, 12:02:49 AM »
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?)
yes I would like to remove that too, I don't want any two of the same words - all should be unique
how many unique duplicate words are there
probably 15-20 words are repeated many times

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 #601 on: March 15, 2016, 12:18:36 AM »
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,";").
I'm getting a formula error
1) Is after pasting this into the module do I need to save it etc or just go straight back to the worksheet?
2) do I not need to remove anything from that text before pasting? (like the first few lines)

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 #602 on: March 15, 2016, 12:31:47 AM »
I'm getting a formula error
1) Is after pasting this into the module do I need to save it etc or just go straight back to the worksheet?
2) do I not need to remove anything from that text before pasting? (like the first few lines)

When you open up the workbook make sure to enable the yellow bar that pops up under the formula bar.

-no need to save
-copy paste everything

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 #603 on: March 15, 2016, 12:47:05 AM »
When you open up the workbook make sure to enable the yellow bar that pops up under the formula bar.

-no need to save
-copy paste everything
not completely sure which yellow bar you referring to but still getting the error

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 #604 on: March 15, 2016, 12:59:49 AM »
not completely sure which yellow bar you referring to but still getting the error
When you open an excel file that has macros, a yellow bar will pop up under the formula bar and ask you to enable macros.

« Last Edit: March 15, 2016, 01:08:09 AM by yitzf »

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 #605 on: March 15, 2016, 01:16:14 AM »
.

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 #606 on: March 15, 2016, 07:43:28 AM »
yes I would like to remove that too, I don't want any two of the same words - all should be uniqueprobably 15-20 words are repeated many times
I think you might be able to create a rule to highlight if contains duplicates

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 #607 on: March 15, 2016, 10:26:02 AM »
.
AMAZING! works like a charm! thank you
ETA: what do I do now when I want to close and save this file with this formula? I can just close it and close the other page where I inserted the module without saving it?
« Last Edit: March 15, 2016, 10:42:57 AM by Jkhein »

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3308
  • Total likes: 519
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #608 on: March 15, 2016, 10:51:26 AM »
AMAZING! works like a charm! thank you
ETA: what do I do now when I want to close and save this file with this formula? I can just close it and close the other page where I inserted the module without saving it?
Modules are saved automatically when you save the workbook (ie, you still have to save the workbook as you normally would in Excel). The VB editor opens as a separate window, but it's one file.

Offline Mordyk

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2013
  • Posts: 3728
  • Total likes: 681
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
  • Programs: Some of this and some of that.
Re: Excel Problem
« Reply #609 on: March 15, 2016, 10:57:35 AM »
i have a formula but instead of the cell returning the outcome it gives me the formula in text format. how can i help that?
#TYH

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3308
  • Total likes: 519
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #610 on: March 15, 2016, 11:00:43 AM »
i have a formula but instead of the cell returning the outcome it gives me the formula in text format. how can i help that?
Change the formatting. Also, click into the cell to make sure you don't have an apostrophe at the beginning of the formula (which would effectively turn it into text).

Offline Mordyk

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2013
  • Posts: 3728
  • Total likes: 681
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
  • Programs: Some of this and some of that.
Re: Excel Problem
« Reply #611 on: March 15, 2016, 11:02:00 AM »
Change the formatting. Also, click into the cell to make sure you don't have an apostrophe at the beginning of the formula (which would effectively turn it into text).
i checked both of that removed all formatting. thanks but i already found a workaround
#TYH

Online 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 #612 on: March 15, 2016, 11:02:34 AM »
i have a formula but instead of the cell returning the outcome it gives me the formula in text format. how can i help that?
try changing the cell format. Also you can try pressing control-~ (it's called tilde)
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 #613 on: March 15, 2016, 11:10:39 AM »
AMAZING! works like a charm! thank you
ETA: what do I do now when I want to close and save this file with this formula? I can just close it and close the other page where I inserted the module without saving it?
My pleasure

Modules are saved automatically when you save the workbook (ie, you still have to save the workbook as you normally would in Excel). The VB editor opens as a separate window, but it's one file.
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 #614 on: March 15, 2016, 11:16:44 AM »
this jsut messed me up. how do i undo this?
Do it again

Offline Mordyk

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: May 2013
  • Posts: 3728
  • Total likes: 681
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
  • Programs: Some of this and some of that.
Re: Excel Problem
« Reply #615 on: March 15, 2016, 11:17:21 AM »
Do it again
done. i deleted the post before you answered. but thanks :)
#TYH

Offline Work-for-ur-muny

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Dec 2014
  • Posts: 3101
  • Total likes: 317
  • DansDeals.com Hat Tips 34
    • View Profile
Re: Excel Problem
« Reply #616 on: March 15, 2016, 02:01:14 PM »
I have a spreadsheet that keeps track of (for example) every time someone enters my store. Column A is the last name and column B is the first name, (with other data entered in subsequent columns). I entered the following formula into column Q:
Code: [Select]
=COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,B2)(I chose till row 1000 because I knew I won't exceed that amount of rows anyway.)
My question is, how can I modify the formula for it to meet the following two criteria:
  • Categorize the customer by category (which is manually entered in column C). IOW if two customers with the same name are entered it will count them separately due to the difference in category; and,
  • It should only count how many times a given customer entered the store within the last two weeks, or any other specified amount of recent time. (Dates on which customers visited are also entered manually in a separate column.)
TIA

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3308
  • Total likes: 519
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #617 on: March 15, 2016, 02:56:50 PM »
I have a spreadsheet that keeps track of (for example) every time someone enters my store. Column A is the last name and column B is the first name, (with other data entered in subsequent columns). I entered the following formula into column Q:
Code: [Select]
=COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,B2)(I chose till row 1000 because I knew I won't exceed that amount of rows anyway.)
My question is, how can I modify the formula for it to meet the following two criteria:
  • Categorize the customer by category (which is manually entered in column C). IOW if two customers with the same name are entered it will count them separately due to the difference in category; and,
  • It should only count how many times a given customer entered the store within the last two weeks, or any other specified amount of recent time. (Dates on which customers visited are also entered manually in a separate column.)
TIA
Just add more criteria into the COUNTIFS. The first item (category) would certainly be a simple extension of what you have already. And the second item (time-frame) would be similar, but you'd end up repeating the condition. For example:

Place the category in Column C, and the date of entry into column D. Your new formula in Column Q would then be:
=COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,B2,$C$2:$C$1000,C2,$D$2:$D$1000,">="&?x?,$D$2:$D$1000,"<="&?y?
where the ?x? represents the beginning of the time-frame you're looking at, and the ?y? represents the end of the time-frame you're looking for. Ovbiously those values would be placed in a fixed spot off to the side somewhere.

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 #618 on: March 15, 2016, 03:05:41 PM »
I have a spreadsheet that keeps track of (for example) every time someone enters my store. Column A is the last name and column B is the first name, (with other data entered in subsequent columns). I entered the following formula into column Q:
Code: [Select]
=COUNTIFS($A$2:$A$1000,A2,$B$2:$B$1000,B2)(I chose till row 1000 because I knew I won't exceed that amount of rows anyway.)
My question is, how can I modify the formula for it to meet the following two criteria:
  • Categorize the customer by category (which is manually entered in column C). IOW if two customers with the same name are entered it will count them separately due to the difference in category; and,
  • It should only count how many times a given customer entered the store within the last two weeks, or any other specified amount of recent time. (Dates on which customers visited are also entered manually in a separate column.)
TIA

For date (where col C is the date they entered)

Code: [Select]
=COUNTIFS($A$2:$A$1000,A3,$B$2:$B$1000,B3,$C$2:$C$1000,">="&TODAY()-14)

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3308
  • Total likes: 519
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #619 on: March 15, 2016, 03:41:36 PM »
For date (where col C is the date they entered)

Code: [Select]
=COUNTIFS($A$2:$A$1000,A3,$B$2:$B$1000,B3,$C$2:$C$1000,">="&TODAY()-14)
Just remember that this only works if you're always using the current date as the endpoint of your range (ie and not an earlier date). You could make this setup variable by replacing that 14 with a fixed reference to a cell somewhere on the sheet that you can enter any number of days in.