Lots Of Text Data, Need To Assign Unique Numeric Values
Aug 28, 2009
Here's my situation.
I will post a rudimentary example:
Account Name/Column A
Truman school, San Diego
Truman school, San Diego
Truman school, San francisco
Truman school, Atlanta
Truman school, Atlanta
In column B, I would like it to assign a unique numeric id based on the TEXT only.
So Truman school, San Diego gets a string of numbers: 1234
Truman school, san fran: 1235
Truman school, Atlanta: 1236
My spreadsheet is about 6,000 records with a great deal of the above situation going on. So school names are identical with different locations assigned. I am trying to import to a database and don't want a lot of duplicate records. That's why I'm trying to assign a numeric value.
View 4 Replies
ADVERTISEMENT
Sep 19, 2012
I have used the advanced filter option in excel 2010 to display all the unique occurences of a list of names (column B) and then used the count function =COUNTIF(A$2:A$21,B2) to count the occurences of each name (column C).
However, excel did not manage to take into account the last 3 entries in column A (mir-23). I assume this is because it is a mixture of text and numeric values. How can I include this information in the grouping and counting?
excel1.png
View 3 Replies
View Related
Oct 3, 2011
I am finding many posting on this topic with unique numeric values and have not come across one in regards to a text value. The essence of the formula is looking through a list for unique email addresses and now I need to up it to a date range and eventually a store # range
I am using the following array formula to establish an overall count:
=SUM(IF(FREQUENCY(IF(LEN('[Data - Deliverables.xlsb]orders'!A2:A5000)>0,MATCH('[Data - Deliverables.xlsb]orders'!A2:A5000,'[Data - Deliverables.xlsb]orders'!A2:A5000,0),""), IF(LEN('[Data - Deliverables.xlsb]orders'!A2:A5000)>0,MATCH('[Data - Deliverables.xlsb]orders'!A2:A5000,'[Data - Deliverables.xlsb]orders'!A2:A5000,0),""))>0,1))
Ctrl+Shift+Enter
The date column is E:E.
The store number column is G:G
View 3 Replies
View Related
Jan 1, 1970
I have created a calendar in Exel for individuals to put in their time at the bottom for everyday I have put in a total. Is there a formula that I can sum the dates, pick a certain time from those date and get my total hours for that particular week?
View 6 Replies
View Related
Aug 24, 2009
If I have multiple entries with different but repeatable text values in one column - how do I count all unique ones ? Is there a function or does it have to be a pivot table of sth ?
View 14 Replies
View Related
Sep 15, 2008
I have an issue with some excel spread sheet I am working on.
I am using Macros, Buttons to assign values to a text box.
When I do not protect the worksheet no problems.
When I include my calculation sub into my Worksheet Change module.
It sort of works but flashes a lot, and takes about 6 or more seconds.
When I dont include it in the Worksheet change module it doesn't flash and seems to work fine.
When I protect the work sheet, in my code I unprotect it and try to run my code, but it wont go through. I get an error.
Run-Time error '1004': Application-defined or object-defined error
Worksheet Module
Option Explicit ' Force the declarations of all variables in this module
Private Sub Worksheet_Change(ByVal Target As Range)
'Unprotect
'Call Calculate_Decision
If Range("MoreBorrowers") = "Yes" Then
Rows("21:27").Hidden = False
Else
Rows("21:27").Hidden = True
End If
If Range("GuaranteeYN") = "Yes" Then
Rows("159:167").Hidden = False
Else
Rows("159:167").Hidden = True
End If
I have tried adding in the Me. before my Range but nothing seems to work.
View 9 Replies
View Related
Jan 31, 2010
I am working up a workbook template that will be used to process survey results. The way I ultimatley want it to work is that the user can paste data from another application into a worksheet in Excel, and then view analysed results on a second sheet (which are processed via formulae on a third, hidden sheet).
The problem I have is this: the data from the program that's capturing them are text values. For example, column B contains answers to a question where participants rate an experience, and the values are either "Excellent", "Good", "Fair", "Poor", "Awful". I want these to be converted to numerical values, on a scale where "Excellent" = 5 and "Awful" = 1, so that I can then average these.
So, the question is: Can I somehow tell Excel that "Excellent" = 5 and "Good" = 4 etc, and then use AVERAGE(B:B) on the text data and get a number back?
I did think of having an intermediate sheet that used VLOOKUP to create a copy of the first sheet (where the user pastes the text data) with the text replaced by numbers. But, because I don't know in advance how many rows will contain data (i.e. how many survey results there will be), I have to assume on the high side and copy down 50,000 rows. But, this takes ages to calculate.
View 9 Replies
View Related
Jun 5, 2014
I'm trying to think of a way to do this. I need a unique ID number per company, so I would want it to look something like this:
0001: Apple
0001: Apple
0002: Google
0003: Sony
0003: Sony
0003: Sony
0003: Sony
0004: Ford
0004: Ford
0004: Ford
.....and on and on for about 400+ rows. I think I know how to state the condition. IF B15 =/= B14 then....assign unique ID, else A15 = A14....or something to that effect.
View 1 Replies
View Related
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...................
View 9 Replies
View Related
Feb 26, 2008
Are test have some text values RA = 0 and A= 0.5 as well as numeric values. I am currently using the formula =IF(COUNTIF(A1:A13,"A"),(COUNTIF(A1:A13,"A")*0.5)+SUM(A1:A13))/COUNTA(A1:A13) which is not very good at all, but it works for this instance. I also use the formula in B2 =IF(A1="RA",0,IF(A1="A",0.5,A1)) as a helper column but I need a stabile formula with out a helper column that will allow me to average text and numeric values. In the future, they are going to expand the text values part i.e. B = 0.3, BD = 0.2 etc....
View 9 Replies
View Related
Jul 8, 2008
I am preparing a very large spreadsheet of text. Once I reach a certain point (a few paragraphs?), the program stops wrapping the text. All of my text is visible in the box at the top of the spreadsheet when I click on the cell. I double checked to make sure it's set to wrap, which it is. I tried merging two cells, no change.
View 9 Replies
View Related
May 29, 2013
I'm trying to find out exactly how I can use sum product on cells that have text and numeric values. Here's my sample data (6 points):
TN FX1576 20, TN FX1577 25, AZ FX1577 30, AZ FX1577 35, FW FX 1577 40, and FW 1577 45.
I wish to do the following: I want to sum all of the right numeric values based on the first two text values(TN, AZ, or FW). I want to sum these numbers to their respective total cells at the top of the page. So far I can sum the numeric values fine using the following formula: =sumproduct((right(range,2)*1) but when I try to differentiate between the respective locations (AZ, TN, or FW) I get an error message.
Attached is an example of what I'm trying to do along with the formula I'm currently using and yellow shading to represent where I want my respective sums to go.
ExA.xls
View 1 Replies
View Related
Jul 14, 2014
I have a list of numbers with decimals : such as
742111.37
703102.4
641771.52
652689.31
741202.92
I would like to convert the Numeric values to Text
Result :
Seven Hundred Forty Two Thousand One Hundred and Eleven and Thirty Seven .....
..........
..........
and so on....
View 1 Replies
View Related
Feb 12, 2009
I am wondering if it is possible to format a cell that contains text - based on a number in the cell next to it. Here is what I have:
Category......Mean
Results.........3.3
Morale..........3.8
Buy-In..........4.0
I would like the word 'Results' to display in BLACK text because the mean score is in the range 3.0-3.9 (same goes for the word 'Morale'). I would like the word 'Buy-In' to disply in GREEN text because the mean score is in the range 4.0-5.0. Basically, the mean scores are calculated using formulas and I would like the color of the category to change based on the mean score number. Is there a way to use conditional formatting to do this? Or another way to automatically make this work? File is attached.
View 2 Replies
View Related
Jan 1, 1970
how to convert number into text (acutal formating)
Eg. : 150500
One Lac Fifty Thousand Five Hundred
View 14 Replies
View Related
Nov 3, 2011
Currently I have a macro set up that sorts a range.
It sorts numbers first in ascending order and then text comes at the bottom of the sorted range.
Is there any way of reversing this so that text appears first and then numeric values in ascending order below?
View 1 Replies
View Related
Jul 13, 2006
I'm attempting to use Excel as a writing tool.. it's incredibly powerful as it will allow me to customize my workspace, create links, flow charts etc... but an area I'm most concerned of is at the heart of this endeavor, which is text... lots of text!
I believe there is a limitation regarding the amount of text within a cell ( something like 250 characters?) Is there a way to customize/format a cell to include much more text?
What route would you advise? ..Forms, pivot tables, textboxes?
View 5 Replies
View Related
May 14, 2012
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].....
View 1 Replies
View Related
Nov 17, 2013
Is it possible to assign text using data validation?
Right now, the three cells I'm using for data validation contain text, but return a number to the linked cell.
View 1 Replies
View Related
Jan 14, 2013
I have a range of data on one worksheet that is pulled into a pivot table on another worksheet. Looking at the table below I would like to assign the values in the pivot table to a 1 if there is data. For example if any field is >0 I would like to assign it a 1. If not leave it blank. I tried using the formula function in the options tab but didn't have any luck.
Car
Truck
SUV
Color
Black
1
15
Red
7
1
Blue
1
5
View 4 Replies
View Related
Oct 2, 2007
I have some code that goes through some data that is imported from a database via a query.
This has been working for a few years without a problem.
I lookup and employee number on my sheet to the employee number in the database table.
this is my code that checks the employee number.
Set rs = Worksheets("Employee")
If rs. Cells(r, 2) = cells(1,1) Then 'if employee number matches
......
The problem is that now the database application has adapted an alpha option (it used to be numeric only). In order for my code to work I have to change the employee number on my sheet by putting a ' in front of it.
Example if the number was 127 I need to enter '127 in the cells.
I have a few thousand in my data.
Can I add some code to my macro so I can still enter just the number in the cells?
View 4 Replies
View Related
Jun 4, 2009
I need to make sure that the two sets of data i have highlighted in yellow and blue are completely the same. Please can some one write a formula that will flag a cell next to the blue column indicating that there is a difference between that of the blue and the yellow columns individual row.
View 3 Replies
View Related
Feb 22, 2012
Is it possible to validate a string with both numbers and text with a formula using data validation? (I don't want to use a User Defined Function)
in Column C, Starting in cell C2 I have a 12 character string of six numbers, four letters, and two numbers. like this:
280838ZNOV11
is it possible to use a Custom Data Validation formula to ensure the user follows this format?
View 9 Replies
View Related
Jun 4, 2008
I'm currrently using column I and col J
in col I are all the units of measurement: KG, PKT, BAG
in ColJ is the formula to return:
KG as 9999999
PKT as 8888888
BAg as 7777777
if(i2=kg, 999999999,"false!")
i was thinking of using if....but triple if formulas would be long isn't it?
PART2 of this would be using:
=IF((SUMPRODUCT(--(db!$A$2:$A$36000=$B$1),--(db!$F$2:$F$36000=C$5),--(db!$G$2:$G$36000=C$6),--(db!$E$2:$E$36000=$A7),--(db!$D$2:$D$36000=$B7),db!$I$2:$I$36000))=9999999,"KG","Invalid")
to check 5 values and if it is 999999999 it'll return KG
8888888 PKT
7777777 BAg
View 9 Replies
View Related
Oct 30, 2008
I know it is possible in Access auto assign a unique ID (primary key?) to each record in a table. I am creating records in a table using Excel and Visual Basic to write to a ".mdb" file without using Access. Is there a way to auto assign a unique ID within the coding?
The reason I ask this is if I had two users writing to the ".mdb" file at the same time, I don't want them to end up with the same ID's for two differend records.
Sub UploadRecord()
Dim DBName, DBLocation, FilePath As String
Dim DBConnection As ADODB.Connection
Dim DBRecordSet As ADODB.Recordset
Dim XLRow As Long, XLColumn As Long
Dim DataRange As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Worksheets("Find Record").Range("A2").Value = Worksheets("Index").Range("H3").Value
Worksheets("Find Record").Range("B2").Value = Worksheets("Index").Range("I3").Value
Worksheets("Find Record").Range("C2").Value = Worksheets("Index").Range("J3").Value
Worksheets("Find Record").Range("D2").Value = Worksheets("Index").Range("K3").Value
Worksheets("Find Record").Range("E2").Value = Worksheets("Index").Range("L3").Value
Worksheets("Find Record").Range("F2").Value = Worksheets("Index").Range("M3").Value
Worksheets("Find Record").Range("G2").Value = Worksheets("Index").Range("N3").Value
Worksheets("Find Record").Activate.........................
View 9 Replies
View Related
Aug 3, 2006
I want to count unique items in a list with an array formula, like the items is in 3 columns A,B,C are from row 2 to 101. Now i use an array to filter out some rows in the columns of B,C & then count unique items in Column A.
so,
column A has "30 diffrent names repeated from A2 to A101"
column B has numbers 0 to 100
column c has value either 0 or 1
now i use an formula TO filter out rows in column B & c
formula =count(IF((B2:B101>0)*(C2:C101<>1),1))) using ctrl+shift+enter
now what should i suffix or prefix to this formula to count unique values in column A.
View 11 Replies
View Related
Aug 1, 2012
I was using SUMIFS on a large amount of data, but wasn't getting back the exact sum that I needed.
It turns out that I had cells with values similar to the following
Code:
000123
0123
Basically, text fields with the same numerical values, except for the number of leading zeros. Due to the nature of the table, the number of leading zeros is important.When I did SUMIFS, it counted the sum of the two values together. For example with the following:
Item
Batch
Qty
ABC
123
1
It appears that even if a numeric value is stored as text, SUMIFS treats it like a number.
View 3 Replies
View Related
Apr 14, 2009
I have a series of worksheets that are formatted for data imported from various scripts.
What I want to do is parse through each worksheet and cell, when the cell has numeric data, I want to clear the cell.
I do not want to clear the cells on the following conditions:
Cell has Text
Cell has Formula
Cell has date, month or time
View 9 Replies
View Related
May 1, 2009
I have a list of Window sizes and types in Range B4:B:43. The descriptions repeat because in Column K I am listing a location for each window in a house.
Example:
B K
3050 SH 1/1 Dining Room
3050 SH 1/1 Kitchen
2030 Fixed Foyer
2030 Fixed Living Room
In the Same sheet Starting on B:45 I want a list of only Unique Window Types:
B:45 and Down:
3050 SH 1/1
2030 Fixed
I'd like this to automatically appear after populating the first list. I used an advanced filter the first time and it worked, but it is not reliable and sometimes returns duplicate values or give me an error message. Plus once again having it happen automatically as the list will change each time I access the sheet would be great.
View 11 Replies
View Related
Mar 16, 2009
I have a problem that i don't know how to solve. I have one cell with values like this: 127;71;512;458;1250;722;2154;742;29;350;
and what to put in lines like this:
127
71
512
458
Has i have a lot of data in the cell and everyday it changes, i take several hours doing it manually. I have tried using the command data/ text in colluns but because i have huge data it leaves a lot out. Do you have any idea how to put that values in lines automatically?
View 4 Replies
View Related
