# Returning Most Occurring Text In A Range

Jun 6, 2014

Store 1
Store 2
Store 3
Store 4
Top Selling
Second
Third
Fourth

[code]....

I am trying to find two formulas:

Formula 1: Formula 1 will be entered into rows 6:9 (in this example). I need it to return the highest selling product (by store) in order form highest selling to lowest selling. I have a makeshift formula already but it cannot report in order (only the highest selling) and it cannot get past the one return index( limitation. When it finds two high sellers it prioritizes the ranking based on how well that product has sold across the other stores. Product 1 sold 13 times across all stores. Product 2 sold 9 times across all stores. So if product 1 & 2 tie for Store 3 (as shown in the example) Product 1 would be chosen based on its sales across the row.

Formula 2 : Same as formula 1 but with Stores (in column 6-9). It reports the highest selling store and treats ties the same way as formula 1.

## Most Frequently Occurring Text - When There Is More Than One Most Frequent

Aug 16, 2012

I am using a formula that finds the most frequently occuring text from a range of cells that have the text 'Ra' 'Ma', 'Mo' or 'Mi' in them (each cell has a drop down list with these text items only). I am currently using this formula to find which of the four text items appears most frequently in the range:

=INDEX(D6:D15,MATCH(MAX(INDEX(COUNTIF(D6:D15,D6:D15),0)),INDEX(COUNTIF(D6:D15,D6:D15),0),0))

This formula works fine in a case where there is more of one text item than any other, but when there are two items with the same number of entries, this formula comes back with the item that fulfils the criteria first - i.e. if 'Ra' appears 5 times and 'Mo' appears 5 times, then either could come back, depending on which appears first in the range.

The text items rate in importance with 'Ra' being the most important, then 'Ma', then 'Mo', then 'Mi' is the least important. What I am trying to do is, in cases where more that one text item appears the same number of times, get the formula to display the one that is the most important. So for example 'Ra' will always appear over 'Mo' if both have 5 entries, but 'Mo' will appear over 'Mi' if 'Mo' and 'Mi' both have the most entries.

## Find Most Frequently Occurring Or Repetitive Value In Range

May 9, 2008

I have a few columns referencing Crash numbers. The majority of each column is a null value, with about 10-20% of it actually containing the number of a Crash. I want to run one MODE each on a column to see what the most common Crash is.

The following
=MODE(Table_X[Crash Alpha])

Results in a #N/A Value Not Available error. What do I need to change to make it work on the column with some nulls and some numbers (no strings)?

As a secondary question, if I wanted to apply a date filter (the columns are part of a table, including a Date column) like the below:

If(Table_X[DateTime]>F9,If(Table_X[DateTime]<F9+1, etc.)

(F9 being the location where my filter Date is)

Where would I apply that to the Mode? Should I just change it to an If statement, with the Mode positioned like so?

=If(Table_X[DateTime]>F9,If(Table_X[DateTime]<F9+1, MODE(Table_X[Crash Alpha]))

## Returning Text Against Text Based Criteria?

Jul 19, 2014

I need to analyse more than 4500 product of my company. I have data in more then 20 separate excel worksheets, I need a function or a vba code to return the text against my product names.

I my main file i have the names of the products, sales and purchase data and analysis. In other files I have the categories list against those products. In the main file the products are according to brand and categories have different products from different brands grouped together. I want a function something similar to sumif but for text.

Example

What I need
Art no. Product Name Brand Group Category Subcategory
y-123 abc lux hair care shampoo hair shine shampoo
A-123 bca loreal skin care anti acne anto acne soap

Main file
Art no. Product Name Brand Group Category Subcategory Q1 sales Q2 sales Q3 sales
y-123 abc lux 120 110 105
A-123 file with categories against respective products.xlsxmain file what i have.xlsxmain file.xlsxwhat i need.xlsx

Category files

Art no. Product Name Brand Group Category Subcategory
y-123 abc lux hair care shampoo hair shine shampoo

## Returning Text Values From Text Or Numeric?

Jun 4, 2012

I have two (2) different values in the same column one value is text (INV) the other is a time date stamp 05/18/2012 10:48:32. The text i want to return in a seperate column for these two is if it is INV then the result is "PENDING" if it is a date 05/18/2012 etc. then the result would be "PAID" example:

Payment Status
05/08/2012 10:30:12
INV
05/17/2012 08:27:37
INV
and so on...................

## Retrieve Next Occurring Value?

Jan 19, 2009

I'm using Excel 2007 and s/s is 325501 rows deep. It consists of a series of approx 30000 ranges between 4 and 30 rows deep.

What I need to do is locate the next appearance of a name and copy its accompanying value to the present occurrence. Doing this manully is not feasible, given the large size of the s/s and I would like to acquire code.

The names are in column B and the values to be retrieved are in the adjoing cell in column C.

The code should only act when there is a number in column S and retrieved values should be placed in column V.

So if XXX appears in B2 and B345 and C345 contains 932, I need 932 to appear in V2.

## Returning A Numerical Value From Text

Oct 25, 2013

I'm trying to streamline my spreadsheet a bit. Sometime last year I managed to use the formula found (on the attached file) in cell K4 to assign a numerical value to a lettered grade. However, it's been a year or so since I thought about it and so I've completely forgotten how to edit it to make it more appropriate for this spreadsheet!

Essentially what I'm trying to do is a workaround for what I've already done in columns E and F: I'm trying to show the difference between two grades (letters) as a numerical value. Is there a way to do this, or is the VLOOKUP work around I've used in columns E and F going to be the simplest way?

## Returning Specific Text Only

Mar 21, 2006

here is my text strong:

PO: Trans:DC:MMU - DO NOT BREAKTICKET:ALLOC:RE ORDER OF # 103157131,
101915744. MMU - DO NOT BREAKAP:

I want to return text starting with ALLOC but stop at and not include AP.

## Looking Up Text And Returning Scores

Jan 18, 2007

| item1, item2, item4 |

Elsewhere I have a set of points for these like this:

| item1 | 4 |
| item2 | 2 |
| item3 | 3 |
| item4 | 1 |

(the |'s represent cell borders, if it isn't obvious)

What I want is a formula that can grab the text, then produce a total score. So, for my example, the result would be 7, totalling the scores for item1, item2, and item4.

I know that the score lookup is trivial (just a VLOOKUP), but I can't figure out how to do the scan for the data from a single cell. I want the items to remain in a single cell if possible.

## Return Most Occurring Name From A List

Jul 17, 2009

Which formula do i use to return a name to me, that is the most occurring from a list?

I tried =mode(range) but i dont think it was the right one.

## Frequently Occurring Numbers?

Dec 28, 2013

Formula to identify the top 10 most frequently occurring numbers in column A.

## Find Column Name Of Most Occurring Value?

Jun 22, 2014

So basically I have 9 columns. I want to count the amount of times "D" is used in the rows below them and then find the column name of whatever column one has the most.

## Returning Text Based On Max Value In A Table?

Apr 14, 2014

I am looking to return text based on the maximum value in a table.

Dog
2

Cat
10

Horse
4

Fish
27

Lion
1

Let's call the column with the animals over it "A" and the one with the numbers "B", and the rows are 1-5 to make explanations easier

I am looking to have a formula return the name of the animal with the highest three numbers (fish, cat, horse), but don't need the numbers. I am pretty confident this would be an index match formula, but I'm not too familiar with those functions.

## Returning Number As Text In IF Formula

Oct 6, 2009

This is probably a pretty basic question, but I can't find an answer in Excel help or on the interwebs. I have an IF formula and I'd like 0 returned if the statement is false (e.g. =IF(A1<>0, A1, 0)); however, I would like the 0 returned as text. I've tried the TEXT function and using ` in different places, but all to no avail.

## Returning A Text In A =minimum Function

Dec 11, 2009

I have 3 columns of numerical data. In my fourth column, I would like to return the minimum value from the 3 previous columns (=min). However, instead of returning the lowest number, I would like it to return the column title (=text) of the lowest number. I haven't been able to locate a function that does this.

## Calculate A Formula Instead Of Returning Text

Mar 28, 2006

I am compiling data from several different, variable, worksheets. I am using
concatenate to add the name of each worksheet into a formula to pull the
appropriate data.

However, it is returning the text of the formula instead of
the results of the formula. How can I make it calculate the result instead of
simply displaying the formula text?

## Returning A Text Value Based On 2+ Conditions

Jun 17, 2009

I'm trying to lookup values that are a combination of text and numbers. The difficulty is I need to use two criteria to look it up. I've been trying to use a combination of Sumproduct and Index,Match but it doesn't seem to be working. I think I need to add some sort of text function in there. Below is some data for example purposes.

A B C E F G
M S Get value from G M S 5G
M T M T 6M
M U M U 7H
M V M V 8K

## Web Query Returning Text But Not Numbers

Oct 3, 2009

I have this web query to retrieve Stock Market data for individual scrips which used to work fine till 2 weeks ago:
[url]

Since then all it returns is the text in the tables. Numbers eg dates and stock prices come as blanks.

These show up alright in the browser (Mozilla as well IE).

Some other tables at this site plugged into query return complete data. eg [url]

I tried many different scrips with same results.

Since it was working fine till 2 weeks ago AND other tables at this site are working fine, I suppose the site changed something.

## Returning Number From Text String

Jul 24, 2007

I have been trying a few formulas to return numbers from a string of text. The problem I am having is that there are more than one set of numbers that I need returned.

Here is an example of the text entered in F2

"K2PT bus structure [2 locations] & strain structure South West of L9L12-12 [1 location] / Use #4/0, std, cu conductor, connector Cat ID#10842 at structure leg & connector Cat ID#9655 at #4/0, std, cu gnd loop to connect #4/0, std, cu gnd loop to str leg."

What I need returned in a cell H2 is the numbers beside the Cat ID#

10842, 9655

I tried a Return Right Value formula, but I think because there are more than one possible value it isn't working properly.

## Count Non-zero Items Occurring Between 2 Known Dates

Feb 12, 2014

For each of my staff I need to count if they worked 15 or more days in the 30 days preceding a statutory holiday. (As a follow on problem... If they have worked 15+ shifts then I'll need to calculate the sum of their hours over the 30 days / 30).

I tried the functions below but they didn't work. (the Name1 column records the number of hours worked that day's shift) The actual count in my test case is 5.

=COUNTIFS([stat],"=STAT",[Date],"<[@Date]",[Date],">=[@Date]-30",[Name1],">0") result: "0"
=COUNTIFS([@stat],"=STAT",[Date],"<[@Date]",[Date],">=[@Date]-30",[Name1],">0") result: #VALUE!
=IF([@stat]="STAT",COUNTIFS([Date],"<[@Date]",[Date],">=[@Date]-30",[Name1],">0"),"NO") result: "0"

## IF Function For Last Occurring Unique Cases?

Mar 26, 2014

I have an excel file that lists individuals who came into the clinic on a given day. I am interested in figuring out the amount of unique individuals in a given month/overall. As this is a working datasheet, I would like a formula that would automatically update this information for me. Here is an example of the file that I am working with: unique case example.xlsx

Specifically, I want an IF function that could place an 'x' in column E depending on if the name in column C is a unique instance. But if the name appears several times in the list, I would also like this function to place an 'x' in column E next to the last occurring unique instance. For example, the name 'Hanna D' occurs 3 times in the spreadsheet I have attached, but I want the 'x' to be next to the most recent incident, when she was screened, as opposed to when she was not contacted (missed/doc forgot).

I am not sure if you can even define a unique case in the IF function, let alone the last occurring unique case.

I am not interested in conditional formatting (highlighting duplicate cases and then placing an 'x' next to the most recent highlighted case) because my spreadsheet has 300+ individuals and it is very time consuming to manually move the 'x' when the same individual appears at a later time.

## Stop #REF! Occurring When Changing Spreadsheet

Jun 3, 2008

Is it possible to stop the #REF! error appearing in my formulae when I make a change to my spreadsheet? I have some complicated spreadsheets that take a lot of re-building whenever I need to make changes because of the #REF! error. I tried turning off automatic calculation and that seems to work, until you calculate, then the errors appear. I find myself copying formulae into a text file and then re-pasting back into the spreadsheet after making the changes - there's gotta be a better way! (Using Excel 2003).

## Returning A Range Of Cells?

Jun 3, 2009

Just for fun (and a massive headache) - I am trying to do the following

=IF(INDIRECT("'"&\$Q\$1&"'!E2")="Table",IF(INDIRECT("'"&\$Q\$1&"'!H2")="Yes","Graph","Table"),IF(INDIREC T("'"&\$Q\$1&"'!E2")="Descriptive",INDIRECT("'"&\$Q\$1&"'!J2")," "))

Where Q1 is the name of the worksheet I want to access.
Where E2 is the type of information I am trying to display (Table or Descriptive)
Where H2 is if it needs a Graph or Not
Where J2 is a comments field

What I am wondering is if the following values are set

Q1= May-09
E2 = Table
H2 = No

How can I get it to return the information contained between cells P2 to S3 on worksheet May-09

## Returning Text If Data Meets Certain Criteria

Apr 24, 2014

IN column J(on sheet 1) i want it to return text (OB) if Sheet 1 column A1 equals Sheet2 Columns A1:A500. And if Sheet 1 column A1 do not equal Sheet2 Columns A1:A500 return text(IB).

## Returning The Displayed Text Of A Date Cell

Nov 20, 2008

Take the displayed date from a cell, in this case formatted as yyyymmdd, and simply display the date as plain text, not converted to the 1900 date system. For example, I have a date cell that reads 20080610, but everything I have tried returns 39609. But I want it to return 20080610 in plain text so that I can use RIGHT and LEFT functions to extract the year, month and day.

## Returning Text From Nested IF And Vlookup Statements

Jul 26, 2006

I am trying to return a text statement using nested IF statements. In order to find the value in the IF statements, I have to use lookups.

Example: ....

## Returning Row / Column Number Of Cell That Contains Particular Text

Jul 16, 2014

I am looking to write a formula that will return the row or column number of the cell that contains a particular text within the string. I know it can be done with script, but need it as a formula. Also, to add another level of difficulty, I'd like it to be a traditional formula, no special keystroke to activate. It will be used within a much larger formula.Ex. If G5 has the string "CURRENT DATE" in it, and I look for "DATE", the formula would return 7 for the column or 5 for the row.

## Vlookup - Referring To A Column Other Than The First And Returning Text

Sep 28, 2006

I have two worksheets. On one worksheet will be a place to enter a five digit number. In the adjacent cell, I want to search on the second worksheet in a three-column area for that same five digit number. The five-digit numbers on the second worksheet are located in the third column and the text I want returned from the second column. I cannot change the order of the columns as the first worksheet will be an "add-on" worksheet to a widely used spreadsheet within our company (this spreadsheet already has the second worksheet formatted).

## Computing The Date Of The Next Occurring Specific Weekday?

Feb 18, 2014

My spreadsheet opens. Cell A1 determines what today is, formatted as "weekday, day month year" (e.g., "Tuesday, 18 February 2014").

I have another cell in which I typically enter the date for the coming Friday. Instead of updating this field once each week, I'd like to calculate the date for the next coming Friday based off the value in cell A1 (to be concise, if "today" happens to be a Friday, then it would calculate the date of next Friday). (Continuing the example above, the coming Friday would be computed as 2/21/2014. If I open the spreadsheet on 2/21, it would calculate 2/28.)

What formula or step(s) to follow to render this value automatically?

## Conditional Formatting With Month And 'Dates Occurring'

Dec 9, 2009

I'm trying to keep track of training dates for employees.

I have my spreadsheet conditionally formatted to highlight dates according to months, based on if the training date is due: next month, this month, last month, or 2 months ago; all different colors.

I used "Format only cells that contain" and then used "Dates occurring." The spreadsheet worked perfectly until this month; evidently it doesn't recognize Jan of 2010 being "Next Month," so those cells aren't highlighted. Current formatting is custom, mmm-yy. I tried different date formats but it didn't change anything.

Is there a better way to do this?