I have a column of text values in which I need to display the ten most
frequently occurring. I tried setting the AutoFilter and choosing "Top 10",
but it doesn't seem to be doing anything. I checked into some functions that
I thought might work, like Frequency and Rank, but those seem to be for
numbers only.
I am trying to find a formula that will count the number of unique entries there. I have tried the solutions posted on various websites to no avail (most recently:
Code:
=SUM(IF(FREQUENCY(MATCH(A1:A10,A1:A10,0),MATCH(A1:A10,A1:A10,0))>0,1))
).
The answer should be 4,457.
Ticket Number
T20110819.0527
T20110830.0339
T20110901.0060
T20110901.0060
T20110907.0042
T20110907.0042
T20110908.0186
T20110908.0186
T20110908.0186
T20110908.0186
[code].....
Is there a macro that will allow me to create text to row from a comma separated cell, but also associate the cell to its left automatically?
The table below exemplifies what I need. The top of the spreadsheet is how my data is currently. The portion after the break is how I would like it to be.
I have over 600 rows that needs this done.
1,2,3,4
B
[Code]....
refer to the attached workbook for reference. I am looking for a function in Sheet1, Column E that will search for the value of Sheet1, Column A within Sheet2, Column A. When a match is found, the function should look across Sheet2, Columns B - V for values of 1. When such values are found, the function should return the associated value from Sheet2, Row 2. There may be multiple values of 1, and as such, the function should separate values with a comma.
View 3 Replies View RelatedI am using Windows XP and Office 2003.
I am looking to automate a process where information is sent to someone in a text format and they in turn transfer that data to an excel file. It basically looks like this (but includes a lot more info):
Service Request #: 123456
Instrument Type: New Instrument
Lot/Serial #: 123456
SR Type: Product Complaint
Service Coverage: Maintenance Agreement
The info on the left (text preceding the colon) is always the same but the values following the colon can change. The excel file has all of the text before the colon and they just enter the information into the cells. I am looking to make it so that an Excel macro (or maybe a word/outlook macro) automatically scans the text document and sends this info to the exact same cells in excel every time.
The text file is actually a message from an Oracle database and I am not going to be able to make any changes to the way the data comes in.
Check the attachment, i could not make out this using vlookup, how to overcome this problem.
test.xlsx
I am trying to combine the text values of several formulas in addition to adding some text (punctuation) in between.
Here is the data that I am trying to combine
C14: 2013
C15: Period 6
C16: Week 1
In Cell C13, I would like to have this value returned: "2013 - Period 6, Week 1"
Is there such a way to do this?
I have a collumn with lots of different text values some repeated. how can i count all these values so that it only counts each value once.
e.g
if in cell A1 i have = "apples"
and in cell A2 i have "apples"
and in cell A3 i have "Pear"
I need a formula which I can extract the specific values from a string of text & values. I included an example in excel.
View 2 Replies View RelatedI 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...................
I have 5 textboxes with values in them.
I wish to take those 5 values and place them into one textbox, a 6th textbox.
An example being your name,address,city,state, zip. Taking those 5 and putting them into one textbox lined up like this:
Name
Address
City
State
Zip
What's the code to take values from multiple textboxes and put em into one line by line?
There should be a formula to extract the largest values and its corresponding values. For example, if column
Products Costs
A1 Pen 200
A2 Pencil 125
A3 Radio 670
A4 Apple 1500
A5 Xbox 222
A6 TV 100
The desired outcome
Products Cost
Apple 1500
Radio 670
Xbox 222
Pen 200
Pencil 125
TV 100
The second goal is extracting the top 3 largest values using a formula.
Sample file is attached for your convenience. Desired outcome is in GREEN.
I am trying to create a spread sheet where I have different columns. The columns consist of name and dollar amount. Names may occur more than once. I would like to scan the column with the names and copy the different names to another column. Once that is done, I am hoping to copy the value or sum of all values associated with that name to the column beside it. I am going to have a long list of names with different values and I would like to sort them and add up those values. I have attached an example of what I am hoping to achieve. I am hoping my 'Sum of Values' is actually summed, I left it longhand for explanation sake.
Name
Value
Name
Sum of values
x
4
x
4+2
[Code] ....
I'm trying to return a count of specific text values in a named array using DCOUNT, and for some reason it's not working when it seems like it should. It's probably something simple that I'm overlooking.
DCOUNT works fine for numbers, but when I search for a text string as a criteria, even though it is entered as ="=textstringhere" DCOUNT does not return the correct count. COUNTIF returns the total count of all matching text strings in the array, but that's not what I'm after.
I've attached a sample workbook : DCOUNT.xls
Cells have both text and values in and I need a formula to sum up only the values.
An example is:
joe 22
jim 33
sue 44
...where the formula needs to produce the answer 99.
I've created an example of what I'm working on since it involves personal information. See Attachment...
ExampleExcel.xlsx
I need to use 2 criteria to determine the Sum value for A2 thru A5. The Account# and the Type# below row 7 need to be the same as those listed to the right of the Sum column (A2:A5). I can change the Account# and Type# to text, but the Sum Range needs to be numbers.
I was able to Sum based on the Account range (=SUMIF(Account,B2,Sum), but I still need to make the Type range part of the criteria.
As you can see in the attachment, A2 = 8 when it should = 2 (as seen in row 8 under Sum Range).
The following times I have in two cells next to each other. 16:55:0917:23:00. I'm trying to calculate the difference between the two times but the problem is the paste special values, values are way different and this is causing the calculations to be off.
View 3 Replies View Relatedi am having a problem here,i Have got two columns one for item name other for item quantity.the quantity field has data like:
2+2+8
5+5
2+9
5
666+98
258
6+89+7+4+2+4+5
.....etc.
i need to calculate the total of all the cells like the first has sum 12,second has 10 ,fourth has 5 .Now i can get the sum by simply putting a " = "
in the start of the cell but the problem is that ive got 8000+ cells putting = manually will kill me.
i have a spreadsheet that has 3 shifts. its a weekly data sheet. i have 52 sheets for every week of the year. so far i have a "data" sheet aswell. this is the first sheet. I use this data sheet to generate the information or the other 52 sheets. example is you enter the date of the first monday of the year and it generates the dates on all of my sheets for the whole year. i would like to do something similar for the shifts. i would like to have a cell the enter the shift of the first supervisor on that first monday and have all the shifts generate. 1=nights,2=days,3=afternoons. but i do not want the number to appear i would like the word. the set up goes like this:
DATA E9 = the first number for shift to generate all other numbers
A5 = cell to display first shift
A22= cell for the next shift
A39= cell for last shift
on sheet1 i have A5 as =sum(DATA!E9)
i would like A22 to be the shift after example if A5 as 2 for days than A22 would be 3 for afternoons and A39 would be 1 for nights.
preferably displayed as DAYS/AFTERNOONS/NIGHTS
I've got a workbook with several sheets, Sheet_1, Sheet_2, Sheet_3 and so on.
Each worksheet has various estimates in them and cell B61 contains a sum
I also have a summary worksheet that contains the sums from each worksheet like so:
Sheet_Name
Sum
Sheet_1
Sheet_1!B61
Sheet_2
Sheet_2!B61
Sheet_3
Sheet_3!B61
In the sum column, isntead of having to manually type in Sheet_1, Sheet_2 and Sheet_3, I'd like to use the value listed in the Sheet_Name column
I've tried concatenating the value in the sheet_name column with !B61 but instead it uses the text value Sheet_1 and doesn't go to the actual sheet.
Is there a clever formula I can try in the sum column?
I have a row in a spreadsheet thus:
Column A Column B Column C Column D Column E
WIN 2&1
WIN 6&5
TOT
0
WIN 6&5
0
Columns B and C contain no data
The above is represented only of a much deeper and wider spreadsheet, but should do for the purposes of requesting what I need to know. I want to be able to report the number of wins in column E, as a sum of the results in columns A through D all of which have alphanumeric characters in them; some may also be blank.
I have tried various functions to try and split, say WIN and 6&5, but have yet to find any solution to the problem.
I have a couple of things to do here which I hope you can help me with.
1. I want to be able to extract a date which can be either dd/mm/yy, dd/mm/yyyy, mm/dd/yy or mm/dd/yyyy from a text string
2. Any value that comes before a '%' sign to be stripped out too.
So for example if I have the following:-
A1: Bank Of Scotland 4.125% mod feg VYF 27/06/09
B1: 27/06/09 (from part 1 above)
C1: 4.125% (from part 2 above)
Would prefer VB to do it but formula solutions very welcome too.
I am trying to setup a spreadsheet, where I am typing text into a cell, but I want that text to represent a numerical value, so when I do an auto sum, it tells me the totals. For example:
PaycheckBills to PayTotal of BillsJune 6, 2008Car- 1Car- 2WaterCableVisa1360June 20, 2008MortgInsuranceMCElectric2500July 4, 2008Car- 1Car- 2WaterVisaMC1460July 18, 2008MortgInsuranceElectricCable2400
Where Car-1 value is 500, Car- 2 is 500, Water is 60, etc...
Is this possible?
I have a column that contains different text values. In another column I have numerical values. I'm trying to create a formula that will first look in the text column for a certain name, if the name matches then look in the number column for a specific number and then count the number of cells with that specific number.
One of the challenges is that the number of names will vary and the names aren't in any type of order.
Is there a way to sum a list that contains both text and values using the SUMPRODUCT function? My efforts yielded the #VALUE! error. SUM and SUMIF will ignore the text but I have multiple criteria.
View 9 Replies View RelatedI have created a form that will be used my many people. The first person will enter their information and click a button to transfer their data to a second workbook, as well as send the workbook via email. At a later time, someone else will add more data to the emailed form and transfer this to the database in the second workbook. What I need is to be able to find the row in the second workbook containing the data transferred the first time and add the data that was put in later to the same row as the original.
View 7 Replies View RelatedI need a formula that will calculate the sum total of 6 and a longer formula to cope with 12 letters in a range. The third value in both ranges is worth double points.
The letters and their corresponding values are:
F=0, P=2, M=4, D=6
Some 6 letter examples to illustrate:
in A3:D3 the letters D,D,D,D,D,D return the value of 42 in E3 (6+6+12 (third letter is worth double)+6+6+6
in A4:D4 the letters P, P, F, F, M, M return the value of 12 in E4
Some 12 letter examples to illustrate:
in A3:L3 the letters D,D,D,D,D,D,D,D,D,D,D,D return the value of 78 (6+6+12+6+6+6+6+6+6+6+6+6)
If the range is empty the formula should leave the cell blank.
I have a workbook with about 20 different worksheets in it. Each one has a header with three text boxes in it. I would like to fill these text boxes for all of the sheets, but I would like them not to be linked together or to any one cell, so that the individual fields can be modified after they have been set.
My code is below. When I run it, I get an error - "Compile Error: Method or data member not found."
hwrev = InputBox("What revision is your hardware?", "HW Revision")
eswrev = InputBox("What revision is your embedded software?", "ESW Revision")
sheetcount = 0
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
I have 2 columns - one that has numbers and the second that has text such as A, B, C, D,and E. I need to be able to convert the text into values such as 1,2,3,4,and 5 and then multiply with the first column that has value and display the resultant.
View 8 Replies View RelatedI'm creating 10 'categories' for Column "C" that I plan on using drop-downs to select from. Column "H" has a value tied to "C". What I want to do, is have Excel SUM all of the values in Column "H" where the Category in "C" is a specific text value (e.g. say a category is "hardware", I want a cell with a formula that sums all of the values in "H" where "C" is "hardware".)
View 8 Replies View Related