Posts by Hoowzer

Quote from bosco_yip

Further to my posted formula in Post #.2,

Should you wanted to return multiple result in respect of the multiple criteria, you need TEXTJOIN function of which available in Office 365 or above.

Then,

In "Map" sheet F2, array formula (confirm pressing Ctrl+Shift+Enter instead of just Enter) copied down :

=IF($G2="Y",TEXTJOIN(", ",1,INDEX(Lookup!$D$2:$G$52,N(IF(1,MATCH(FILTERXML("<a><b>"&SUBSTITUTE(B2,",","</b><b>")&"</b></a>","//b"),Lookup!$C$2:$C$56,0))),MATCH(C2,Lookup!$D$1:$G$1,0))),"")

and,

Trailing space in B12 should be removed

Regards

Display More

Thanks Bosco_yip. Unfortunately I only have Office Pro Plus 2016, but your first formula worked wonders for me.

Appreciate the help Sir

Hi Folks -

Struggling to find out how to make this work so wanted to reach out.

On the attached spreadsheet I'm trying to return Column F (Rep Firm) based on some criteria. What I would like to happen is for an equation to look at Column G. If Column G has a:

• "N" - then nothing needs to happen
• "Y" - then look at what is in column C (Channel) and do a lookup on the 2nd tab (Lookup) and display the right firm based on that state (Column B) in that row

For Example for Row 2 -

• Has a "Y" in Column G
• Column C reflects it is "HVAC"
• Column B reflect State is KY
• Lookup value in Column F should reflect "Miller Components"

Thanks in advance for the help!

Wow, this is awesome. I appreciate it! Will spend some time reviewing it!

You're amazing thank you! I think it will cut about 50% of my time now. One tiny request that will make it save me 80% of the time, instead of returning "match" can it return the actual cell number it's in so I don't have to hunt for it?

Thanks!

Hi everyone -

I'm working on a project for my youth group and I'm a bit stumped. Hoping someone can help here.

I have 2 columns in the attached (look at the first tab).

I want to do a vlookup to match if one cell contains another. Because vlookup right now only looks at exact match, I need partial matches. I'm guessing a Macro would work since it's similar to your Ouija board because it breaks up the words.

So in this example I want to look at column B and then look at column E to find partial matches.

Example: B501 has "John Bosco" so does e27. So I want a "vlookup" of some sort on column C 501 to say "John Bosco" or return "cell 327"

As you can see the actual text of the two cells are different, but they have "John Bosco" in common.

I know this can get complicated but as long as 1 word matches another I'm okay with it because it saves me 80% of the time, and I can check the rest. The key is for it to show all the matches, not just one.

Vlookup has to match exactly and returns only 1 answer. I need it to match partially and return all the partial matches...

Any help would be appreciated

Re: Help to determine top sales person

Thanks so much! Super thankful for your help here.

Re: Help to determine top sales person

Brilliant! Thanks so much. I was going nuts because I usually would just use a pivot table. Really appreciate the help!

Hi-

I need help on the attached excel file to see if there is a formula out there to find the top sales person by sales and units.

Would like to see if there is anyone who can help here..

Re: Splitting a large text file based on field values

Hi Pike -

Please see sample here:

https://drive.google.com/file/…zLXNtLTg/view?usp=sharing

Hi Folks -

I have a problem and I've been trying to figure out but have't been able to.

I get a huge file from my partners (roughly 400MB) and I'm trying to find an automated way of splitting that file up based upon the field contents of one column i.e. the column that has the states.

For example - I want to get every row from GA and split it out into excel, and then do that for every other state.

Historically I used a CSV splitter then filtered the file, but that just takes way too much time.

Any thoughts on how to do this?

Hi Excel Gurus -

forum.ozgrid.com/index.php?attachment/62920/

Please see excel file above. I am trying to analyze data in 2 different sets for a marketing exercise.

Columns A-F is the first step where I am trying to determine which mode of marketing works the best - Direct Media or Social. I wanted to ensure the scores did not happen by chance so ran a test and have the p values in Column D. Column F was a function that includes conditional formatting and if it is highlighted in red I do not use either mode because they are not significant. Am I interpreting this correctly? Is there another function I can use to do this automatically?

Scores are on a scale of 1-99 where 1 is the best score and 99 is the worst.

Columns I - W is the 2nd step where I am trying to determine which customer preferences I want to advertise with (Music, sports movies). I also ran a t test and got P values in column M-O. My conditional formatting and functions are in Column U-W. What I really want to know at the end of the day is what preference do these personas have? I'm not sure how to interpret this using the P values...can someone help with a function that will show this?

Hi Excel Gurus -

forum.ozgrid.com/index.php?attachment/62919/

Please see excel file above. I am trying to analyze data in 2 different sets for a marketing exercise.

Columns A-F is the first step where I am trying to determine which mode of marketing works the best - Direct Media or Social. I wanted to ensure the scores did not happen by chance so ran a test and have the p values in Column D. Column F was a function that includes conditional formatting and if it is highlighted in red I do not use either mode because they are not significant. Am I interpreting this correctly? Is there another function I can use to do this automatically?

Scores are on a scale of 1-99 where 1 is the best score and 99 is the worst.

Columns I - W is the 2nd step where I am trying to determine which customer preferences I want to advertise with (Music, sports movies). I also ran a t test and got P values in column M-O. My conditional formatting and functions are in Column U-W. What I really want to know at the end of the day is what preference do these personas have? I'm not sure how to interpret this using the P values...can someone help with a function that will show this?

Re: Change conditional format script

Quote from royUK;726754

Your code will loop 10000 times, how can that be better than simply manually setting up the format?

So are you implying it's easier to do 10000 rows manually vs a script?

Re: Change conditional format script

Well, I'm trying to do the conditional formatting by rows rather than manually doing it....

Is there a way to change the below script so that it does not highlight values that are "0"?

Sub Conditions()Dim myrange As Range








For i = 1 To 10000




Set myrange = Range("A" & i & ":" & "O" & i)
myrange.FormatConditions.AddTop10
myrange.FormatConditions(myrange.FormatConditions.Count).SetFirstPriority




With myrange.FormatConditions(1)
    .TopBottom = xlTop10Top
    .Rank = 3
    .Percent = False
End With
With myrange.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 255
    .TintAndShade = 0
End With




myrange.FormatConditions(1).StopIfTrue = False
myrange.FormatConditions.AddTop10
myrange.FormatConditions(myrange.FormatConditions.Count).SetFirstPriority




With myrange.FormatConditions(1)
    .TopBottom = xlTop10Bottom
    .Rank = 3
    .Percent = False
End With
With myrange.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .Color = 15773696
    .TintAndShade = 0
End With




myrange.FormatConditions(1).StopIfTrue = False




Next




End Sub

Re: Find text in a string &amp; copy to the end &amp; paste in new column

Quote from NBVC;720850

Do you mean?

=IFERROR(MID(A2,SEARCH("Column",A2),255),"")

The IFERROR() returns a blank if the word is not found.

You can replace the "Column" with a cell reference containing the search word.

You can also replace SEARCH with FIND if you want it to be a case-sensitive search.

Display More

Ah, yes! Brilliant.

Thanks for the help

Hi all -

I'm trying to do the following.

Look in a test string in column A "(The blue jean company in Column N)" and find the word "column."

Once the word "column" is found copy the word column and the remaining text into another cell.

For example the output should be "Column N" in the new cell.

Is something like this possible?

Re: Fixing a script to delete N/As in large data sets

Thanks so much - this helps tremendously!

Re: Adding progress bar to code

Hi Smallman - quick question for you. This script works well on smaller data sets, but once I start trying to use this on sets of data ranging from 100-500K it goes through the process of filtering, but nothing remains afterwards. I'm confused as to why it works on smaller data but not larger data sets. Any ideas on what I can do?

Re: Fixing a script to delete N/As in large data sets

Thanks so much!

Is there a way to make this faster? Using it on over 300k lines takes a while!