Excel Help and Problems

[Jkhein]

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

[Jkhein]

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)

[yitzf]

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

[Jkhein]

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

[yitzf]

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.

[yitzf]

.

[churnbabychurn]

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

[Jkhein]

.

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?

[skyguy918]

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.

[Mordyk]

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?

[skyguy918]

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).

[Mordyk]

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

[etech0]

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)

[yitzf]

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

[churnbabychurn]

this jsut messed me up. how do i undo this?

Do it again

[Mordyk]

Do it again

done. i deleted the post before you answered. but thanks

[Work-for-ur-muny]

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

[skyguy918]

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.

[yitzf]

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)

[skyguy918]

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.