Verifying Text Entries
Jul 18, 2009
I was given two files from an old DOS pos system to make a current mailing list with.
The first file is a list of all the customers from this company(full list), the 2nd is all the customers active within the previous year(active list). I don't have any access to any information other than what is on the 2 lists I was given and I need to use the active list to make sure that only the current customers get sent anything. Not normally a big issue, if it weren't how these lists were formatted when given to me. The full list is a mailing list that was formatted to run labels from a dot matrix printer and imports to excel in the following manner,
John & Jane Doe
1234 NE Road St
Someplace, OR 97056
The active list is formatted like:
Doe | John & Jane
Because there is no address information on the active list I can't just generate a full mailing list. I am able to seperate the values from the full list by referencing cells onto another worksheet (it seperates as 'name' 'address' 'city/state/zip') but I need to find a true/false formula, or something similar, that will tell me which names on the full list are also on the active list. I can do it the other way around, and check to see which names from the active list are on the full list (all of them) but because of how the values are seperated on each worksheet I can't seem to figure out how to make this work.
View 9 Replies
ADVERTISEMENT
Nov 13, 2009
I have many text boxes on a form and if any of them are empty then I want a msgbox to popup and exit sub. I know how to do the following
If txtExample1 is "" then msgbox and exit sub
if txt....2
if txt...3
Is there anyway to group the text boxes to test for empty so you don't have to list 20 textboxes
View 3 Replies
View Related
Aug 21, 2014
I have 1 list with two columns, where column A is the location (a 3 digit code), and column B is the name (a 4 digit code).
I have a second list with two columns, again column A is the location, and column B is the name.
I want to make sure the locations and names in the second list match what I have in the first list. If they don't match, I somehow want to flag this in a third column.
View 4 Replies
View Related
Oct 6, 2009
I would like to create a formula that would verify that specific work shifts have been covered each day. The spreadsheet has already been created by someone else, so I am hoping not to recreate the wheel.
The goal is to make sure that all desired shifts are covered with a result in the last cell of the column that would indicate "covered", "not covered" or even a true or false statement.
As an example, an 8am shift is needed. The choices are 8A or 8ALEAD and only one of these is needed for each day.
Can something be set up with conditional formatting from a master list of required shifts.
such as: 7OR, 730*(for just 730 and 730LEAD), 11A, etc.
This is just a snippet of what the schedule looks like: ...
View 14 Replies
View Related
Jun 12, 2014
I have the following two columns, and would like to obtain for each individual Company, the corresponding Country values excluding duplicates as text in a single cell.
Company 2Country B
Company 2Country C
Company 3Country C
Company 3Country C
Company 5Country A
Company 5Country C
Company 5Country C
For example:
- For Company 2, a cell containing "Country B, Country C"
- For Company 3, a cell containing "Country C"
- For Company 5, a cell containing "Country A, Country C"
I've approached generating an array using an IF statement, as in =IF(INDEX(A1:A8="Company 5",,),INDEX(B1:B8,,)," "), which returns the following array: ={" ";" ";" ";" ";" ";" ";" ";"Country A";"Country C";"Country C";" ";" ";" ";" "}.
The question is: how do I get that array to produce, as text in a cell: "Country A, Country C". Note that the duplicate Country C has been removed.
There are a few "StringConcat" User-defined functions that I've found elsewhere on the internet, but they don't seem to be able to handle to conditionally generated IF Index array, which I would think is key to parsing between Countries corresponding to each Company in the list.
View 3 Replies
View Related
Mar 30, 2007
Is it possible to count Text & Non Text Entries in the following way?
The value in cell A1 = 18A
The value in cell B1 = 18B
The value in cell C1 = 19
A Cell with a value that includes and A or a B constitutues a half of a unit so -
The sum of A1 on its own would be .50
The sum of B1 on its own would be .50
The sum of C1 on its own would be 1.0
The sum of B1:C1 would be 1.5
View 9 Replies
View Related
Jul 18, 2014
I want to learn how to program excel to do this :
1) Type in a text data in a predesignated entry box
2) Excel automatically inserts that text into a table
3) If that text is already in the table, then adds a counter next to it (or increases the counter by one)
4) Excels then clears the text data in the entry box
Basically, I want to tabulate the number of times a specific entry comes up, and it would be much faster if I can just type it in, press enter, and let excel do the rest. It would be ideal if I can also add a date to that entry, so the data table comes up to something like a date vs the entries table, and the intersection would be the number of times each entry comes up on that day.
View 6 Replies
View Related
Jun 16, 2009
I created a UserForm to populate client billing info and job location info (so it's a two-parter). A clicked button will then populate a spreadsheet with all of the information. That part works fine.
When the client billing info is the same as their job info, I'd like to offer a checkbox that will populate the job info with the billing info rather than having to type everything twice (like you see on online billing and shipping orders).
View 5 Replies
View Related
Apr 30, 2008
I am writing to a text file and only want to allow 10,000 lines. There is only one value on each line. How can I trim the top of this text file to allow for more entries to be appended to it and still keep it at no more that 10,000 entries?
The lines look like
"1001-1"
"1002-3"
"1003-3"
"1004-4"
"1005-5"
"1002-6"
"1001-10"
"1004-7"
"1015-5"
"0002-6"
View 14 Replies
View Related
Mar 14, 2014
I have a list of names that are mixed and in no order (and need to stay out of order, so not sorting allowed). I need to parse this list to give me the FIRST instance that each name shows up in the list. The one exception is that the first name, cell A1, will be auto-generated from a different workbook, and it is automatically named Name1.
So, in the example spreadsheet, Name1 (cell E2) must equal "Alice". The problem arises on Names2-4. Name2 (cell E3) should be David, Name3 (cell E3)=Jerry, and Name4 (cell E4)=Mark. I tried an array formula:
{=INDEX(A2:A13,MATCH(TRUE,A2:A13<>Name1),0)},
But it is yielding "Jerry" as Name2, when it needs to be Name3. And, of course, this doesn't work on Names3-4 at all. I don't think it matters, but just in case, A2:A13 will be data validation lists.
View 5 Replies
View Related
Aug 23, 2007
I think it's an easy one but for the life of me I can't get around it. ccasionally while re-typing data in fields with the intention of saving-as a new file, an error message appears saying "no more than 20 characters" This happens even when the number of characters is LESS than 20! I have tried clearing cell contents, copying & pasting, re-setting conditions/parameters for columns/rows etc., and nothing seems to work with any consistency. Instead of looking for a work-around solution I'd like to find the actual corrective action.
View 5 Replies
View Related
Aug 27, 2007
I have a user form with 10 textboxes on and when you press the command button, VBA will send the values in each text box to a cell on the sheet. If a user only wants to enter one value, it zeroes all the others in that row!! Does anyone have any ideas please in very very basic lamens code!! It is only relating to the user form called "Figures1". I had an if command running in the "decweek1" modules, but it makes my procedure too long to do the same with the other one!
If you look at my code, you can see how unadvanced I am with this so really simple code would be gratefully received!! If anyone feels nice enough you could always write the code for this problem for me and send me back the workbook, however I don't expect anyone to do this!
It is too large to attach so it is here:-
[url]
View 7 Replies
View Related
Feb 14, 2009
i want a function that tell me how many duplicates are in the ID for a particular text.
View 9 Replies
View Related
Jan 23, 2010
Please refer to attached spreadsheet
I receive data from an external source and it displays what appears to be dates in column B.
In column G I apply a formula to display month/year info.
It turns out that some of the data in column B is valid date data, but other data imports as plain text and therefore I can't get the month/year info that I require.
I have attached just a small example.
I get this data monthly and it usually covers thousands of rows and therefore impractical to change manually.
View 6 Replies
View Related
Feb 28, 2009
I need to assign to a variable, the total number of text entries in col. A (or alternatively in a named range).
View 5 Replies
View Related
Sep 6, 2005
Counting Unique text entries in a sheet with a condition ..
View 14 Replies
View Related
Aug 11, 2014
I need to count the number of unique text values in the first column given a condition in the 2nd column. For example, the formula that I'm looking for should give a result of "3" because it is an SME, and there are 3 unique companies that are classified as SMEs.
Company Name
Company Type
Company A
SME
Company B
SME
(I cant seem to make the tables visible but Company A and Company D right below the header should be in one cell) This should still yield a value of "3" despite having Company A and D in the same cell.
View 5 Replies
View Related
Nov 2, 2011
I have a column that is filled with different names. Some names appear more than once. There is one name in fact that appears four times.
I am looking for a formula, or VBA if that's necessary, that will look through the range Y2:Y50, and return a value of 4, because the person that has the most duplicates, her name appears 4 times.
If everyone's name appeared only once, except for a person whose name was in that range twice, then the formula would return a 2.
View 6 Replies
View Related
Jul 24, 2014
Basically I first want to be able define around 100 variables such as:
Dog = 1
Cat = 2
Mouse = 3
And so on. The reason being my final data needs to be the numbers for it's purpose but because there are so many I can't possibly remember them all to just type out 23 | 44 | 76 | 2 etc. What I would like to then happen is that whenever I type out the words in a cell it's automatically replaced with the number ID.
So typing in
Dog
Mouse
Mouse
Cat
Dog
would automatically result in
1
3
3
2
1
In those cells when I hit enter or at the end in one go. Is this at all possible in Excel 2010?
View 1 Replies
View Related
May 7, 2014
What I need to do is sort certain entries in longer list (column A, it is in .csv format and needs to be in it so coordinates and names and ID, all sorted with commas) and I have another list (column C) which is shorter list of certain IDs. I googled and tried and got some results for the basic structure but the fuction seems to fail. It doesn't matter how I get that third list done, but there is only one criteria: since the list in column A is really long and those entries need to keep the .csv formatting, the function should copy that info what is in the matching cells.
Let me try to put it simple: .csv cells from column A that have matching ID from column C should be copied to column B (or N).
the function I'm working with right now is
=IF(ISERROR(FIND($C:$C; A1))=TRUE;"0";A1)
View 13 Replies
View Related
May 29, 2014
I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.
Example List
Fruit
Color
Apple
[Code]....
View 9 Replies
View Related
Jan 21, 2009
I have a spreadsheet that i update weekly. i need is a formula that will just sum the last four weeks. Example column A rows 1-52 will be updated each week in row 53 i just want the last four weeks to be summed.
View 5 Replies
View Related
Jul 29, 2006
I would like help with a worksheet.
I have a column of Data which is a "material description".
In an adjacent column I have the square meterage of the material.
I would like to total all the square meterage of each material and have it shown in another column.
How can I do this?
I have searched far and wide for an example however I am probably using the wrong terms in my search.
Attached is a sample workbook.
View 9 Replies
View Related
Aug 11, 2014
To give this a bit of context, I am looking at running speed of an athlete. I am trying to determine the NUMBER of sprints in a session (aka >15 km/hr for example). To do this, I need to calculate the number of times a speed vs time graph surpasses 15km/h.
For example, using this data:
TimeVelocity
0.212
0.413
0.614
0.815
116
1.217
1.416
1.615
1.814
214
2.212
2.410
2.613
2.814
315
3.216
3.417
3.618
3.817
415
4.214
4.413
4.612
4.812
511
I would calculate two sprints (one beginning at time point 0.8, one beginning at time point 3). I can't work out how to calculate these sprints independently, rather than counting the total number of entries above 15km/hr (11 in this example).
View 3 Replies
View Related
May 16, 2009
I have a row of golf scores "I3:X3" they either have a number value or "DNP".
I want to SUM only the last five number values and exclude the rest. The list will get a new entry every week. ie I want to SUM the last 5 most recent scores. There may be 15 or 16 scores or only 3, but I want the last 5 (or less) most recent.
View 6 Replies
View Related
Jun 12, 2009
If i have the following chart, what formula do i need to add all the entries EXCEPT and entry OS. i have the formula to add the entries but i need to exclude any "OS" entries. Like the following, but i want it to total 6. (minus the 2 OC entries)
=COUNTA(D6:D15)
TS
TS
VR
IS
IS
OS
VR
OS
8 (total)
View 7 Replies
View Related
Jun 10, 2009
Hello all, I was wondering if anyone would be able to help with a macro or even formula that would remove the entries in Col A that appear in Col C. Attached is an example with dummy data, Col D is how the list should be. If a number appears in A but not C then all instances of it should be kept, conversely if it is in C but not A then it does not need to be kept. Most importantly I need to be able to delete those that appear in both by the same number of times they appear in C.
Hope this all makes sense. Please feel free to ask if it is not clear.
View 14 Replies
View Related
Oct 28, 2009
I m writing the numbers 1 to 100 in rows of 10. I now need to change the code to offset this, starting at any cell i choose.
if i was to type e.g D6 into cell A1, the numbers would start at D6.
View 3 Replies
View Related
Nov 23, 2006
If I have a multiple entry of a product with the same end date against a customer number how could I flag this as something I need to look at?
Eg
Cust number Product Number Expired Date
1000018000 31/12/2006
1000018000 31/12/2006
10000180002811/2006
1000018001 31/12/2006
1001020010 31/12/2006
1021518000 31/12/2006
So in this case the first two would be flag with say “yes” and the others “no” as the customer number, product number and expire date don’t match.
View 9 Replies
View Related
Jul 18, 2007
I'm trying to evaluate the last 8 entries of a particular column of data that I regularly update (K4:K5000). This data is filled with numbers ranging from about -50 to 50 and will occassionally have a blank entry.
I'd like to evaluate these last 8 entries using a couple of different conditions.
#1 - In column D4:D5000, I have three entry values - X, Y, or Z. It will never be blank. I would like for the Excel to automatically review the last 8 entries of columns K and D and do two things...
a. provide an output of a count of how many X, Y and Z there are for the last 8 entries (format: 4-3-1)
b. provide an output of an average of the values of K for each of X, Y, and Z... (i.e., avg of X = 4.4; avg of Y = 2.1; avg of Z = 10.7)
#2 - Next, I would like to add a third column to the mix (Column C4:C5000 with entries >0 and 0, Y>0, Z>0 there are for the last 8 entries (format: 4-3-1)
b. provide an output of an average of the values of K for each of X>0, Y>0, and Z>0 (i.e., avg of X>0 = 9.4, etc.)
View 9 Replies
View Related
