Removing Duplicate Values From Column - Case Sensitive
May 22, 2013
Suppose I have some data in a column like below
Blue
Red
Green
green
red
RED
BLUE
I need a macro or excel formula so that it will remove duplicate products- case sensitive(removing all duplicate prod using using excel,that i know.) i.e. it should not consider Blue BLUE as duplicate. only consider Blue Blue as duplicate.
So the macro should work same as data -> remove duplicate function already exists in excel, except it will be case sensitive..
View 7 Replies
ADVERTISEMENT
Jun 8, 2009
I have been battling with excel for weeks now trying to get some data to display the way I want it to.
The plan is to enter (into individual cells) some letters, some lower case, some upper case ie, A A a A a C d. Some of these will be displayed together in another cell like this: AAaA, AaCd. My problem is that I want them all to be displayed Upper case letters first, lower case second like this: AAAa, ACad. No amount of my inexperienced fumbling with excel's tools has worked so far.
I am playing around with a table for genetics so if my results in a cell are aAbBCcdD, I need it to display: AaBbCcDd. Is it possible?
View 14 Replies
View Related
Jul 1, 2006
I have a spreadsheet which I use to count the occurrences of a value against a range. Basically if the value is uppercase I count as 1 and for lowercase 0.5
This spreadsheet is used as a holiday chart and I have previously used the formula below however, for some reason it no longer works. Not even if I change the lookup value.
The exact function takes a single cell as its value but I have a spreadsheet which allows a range. Although I cannot change it….
=SUM(IF(EXACT(B1:AF99, "LB"),1))+SUM(IF(EXACT(B1:AF99, "lb"),0.5))
View 3 Replies
View Related
Dec 17, 2007
in making this macro non case-sensitive. So when a user searches a company name in the worksheet they do not require to type in the company name exactly as it appears.
For example; when searching Microsoft they can type "microsoft" and the macro would take the user to Microsoft.
I would also like to know if it is possible to add a feature that keeps the search dialogue open so the user can search the next possible match. If the user was to type in "mirco" and the search would show the user any company name with the word micro in it.
Sub Button3_Click()
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer
View 9 Replies
View Related
Nov 2, 2006
is it possible to make a SUBSTITUTE finction non- case sensitive?
For example I want to replace all letters "e" and "E" in a cell.
View 3 Replies
View Related
Aug 12, 2007
I have recently taken on the task of creating a large protected program (without macros) that automatically updates a PowerPoint presentation. I created corresponding workbooks for each sheet in the original (master) workbook. The corresponding workbooks are then used to paste object into the power point presentation. The only problem is it will not update format (text color) changes into the corresponding workbook. I have solved half the problem with conditional formatting, but I want to conditionally format the color of text to two different colors blue if Aa and Red if all CAPS. how to differentiate between Standard word and All CAPS in the conditional formatting.
View 7 Replies
View Related
Feb 25, 2008
I need a macro that will format a a column based on content that is case sensitive. Example, a cell that contains a capital 'P' will be shaded gray with red text where a cell that contains a lower case 'p' will not change format.
View 3 Replies
View Related
Sep 12, 2009
Is it possible to have this type of case sensitive sorting (first all words beginning with a capital later and then all cells beginning with a lowercase letter):
A
B
C
D
E
F
G
a
b
c
d
e
f
g
View 4 Replies
View Related
Jan 17, 2014
I've 2 lists that contain similar info but 1 of the lists holds info I need to join.
I've a unique case sensitive id that I'd like to use to join the lists.
I usually use VLOOKUP but it is case insensitive and I cannot understand how to make it case sensitive.
The 2 lists are in 2 separate worksheets as they each hold a lot of data.
View 3 Replies
View Related
Aug 15, 2007
I have a user form to get some input from the user and want to make sure that in some textboxes user should be able enter only text i.e A to Z or a-z no numbers or special charecters.
Private Sub CommandButton1_Click()
Dim RegEx As Object
Dim Strng As String
Strng = CStr(Me.TextBox1.Value)
Set RegEx = CreateObject("vbscript.regexp")
With RegEx
.Pattern = "^[A-Z]{2}/d+/d{2}$"
If Not .test(Strng) Then MsgBox "Invalid Format: TextBox1"
End With
Set RegEx = Nothing
End Sub ..............................
View 9 Replies
View Related
Mar 2, 2012
I am using the this formula to look up data using 0 to show not found. VLOOKUP is acting case sensitive. The Master Array data is in caps and the input is in lower case. There are no spurious spaces. Here are the results:
Code Name
0 River Branch Foundation = not found
X RIVER BRANCH FOUNDATION = found - in array as caps
The formula:
=IF(ISNA(VLOOKUP(B2,'[Master Array - 2012.xlsx]Master Array'!$A$2:$B$3053,2,FALSE)),0,VLOOKUP(B2,'[Master Array - 2012.xlsx]Master Array'!$A$2:$B$3054,2,FALSE))
View 4 Replies
View Related
Aug 25, 2013
I have an issue with case sensitive while creating forms in Excel VBA. When i compared the text "EXCEL" and "excel" it showing as both the words are not equal/same.
Is there any code to compare the words/inputs without case sensitive.
View 2 Replies
View Related
Apr 2, 2013
I have a data set that I add information to weekly. I then add a value at the end of the row, example active or inactive. I've found that, when using the remove duplicate function, it does not always remove the second instance, resulting in a loss of that added data(active or inactive). So I guess my question is, can I somehow specify which instance of duplicated data is removed?
View 6 Replies
View Related
Dec 8, 2013
I'm looking for a way of keeping case sensitive data in a range of cells, before using Data Consolidate, which when merged afterwards, Consolidate removes the case sensitivity and combines the quantities into one.
Check out this simple table as an example >
A
B
C
D
E
F
G
H
1
TEXT
QTY
TEXT
QTY
TEXT
QTY
TEXT
QTY
2
and
3
and
2
and
10
and
5
[Code] .........
Cells A1-B7 and C1-D7 are two sets of original data, before consolidation.
Cells E1-F4 are the result of applying a Data Consolidate operation to the A1-B7 and C1-D7 ranges - note how the merged result ignores the case sensitive condition in the original ranges.
Cells G1-H7 is the post-consolidation result that I'm looking for, where the original text case is maintained.
View 3 Replies
View Related
May 28, 2014
I'm trying to write a simple VBA code to loop through values in the range A14:A138 and based on the value (of a possible four values) in any row of that range, populate the adjacent column in the same row with a conditional result. For example if A14 = "Cat", then B14 = "I"; if instead A14 = "Dog", B14 = "II", etc. If there is no value in column A, the result should be blank (i.e. "").
I believe are in coding the destination range since I can get it to work for just one cell in B! Below is my code that is not working...
[Code].....
View 7 Replies
View Related
Aug 22, 2012
I need some code that will find the column with the header of "Gender" and change the values from "M" and "F" to "Male" and "Female" but I keep running into issues.
I want to use a Select Case statement so I can set all other found values as blanks.
View 5 Replies
View Related
Jun 5, 2013
I need to remove values from column A that are present in column C without modifying the order in column B. To be more explicit below is an example:
Column A---------Column B-------Column C
5230400----------81,50----------1660000
3275500----------78,00----------6245700
2856300----------47,50----------5230400
1879800----------62,50----------2497900
2124300----------99,00----------1879800
2497900----------65,00
4826900----------77,50
2124200----------34,00
6245700----------61,50
3324400----------86,00
1660000----------53,00
Column A represents 600 materials.
Column B represents 600 prices. (different for each material)
Column C represents 107 materials that are present in column A and have to be removed along with their prices in column B.
Now i want to remove the materials that are in column A and C along with their prices, so i won't damage the order in the file.Meaning if i delete a row in A and shift the other materials up or down, and in the same time column B remains unchanged, the remaining materials will have different prices...and that's not good.
View 4 Replies
View Related
Apr 3, 2013
removing duplicate names. Students were allowed to take a quiz as many times as they wanted. I need to remove the duplicate entry by keeping the highest grade.
Here is the setup of my excel file. Column 1 has surnames, Column 2, has first name, and column 3 has grade.
I can't figure out how to filter them based on first and last name because some students have the same name. with the grade as the criteria
I would need an excel formula not macro
View 9 Replies
View Related
Dec 6, 2008
In Column A, there are about 2000 plus entries, is there a simple formula to highlight the duplicate values in conditional format, so that the duplicate values stand coloured.
View 2 Replies
View Related
Oct 26, 2013
find the attached Sheet, where some values are entered in column A with repeated action. What I need that through an excel function the repeated values should get red colour like in the Column C.
View 1 Replies
View Related
Jul 25, 2007
I have a column of data where duplicate records appear, can I get a macro to review the column and delete the duplicate record, I need the line item number to still show although the serial number has been deleted, please see example below-
line_number Serial_number
14FM12A1QWINC-1A
14FM12A1QWINC-1B
14FM12A1QWINC-1C
14FM12A1QWINC-1D
14FM12A1QWINC-1E
14FM12A1QWINC-1F
14FM12A1QWINC-1G
14FM12A1QWINC-1H
14FM12A1QWINC-1I
14FM12A1QWINC-1J
14FM12A1QWINC-1Jdelete serial number in column B, keep line number 1 in column A
24FM12A1QWINC-2A
24FM12A1QWINC-2B
24FM12A1QWINC-2C
24FM12A1QWINC-2D
24FM12A1QWINC-2E
24FM12A1QWINC-2Edelete serial number in column B, keep line number 2 in column A
34FM12A1QWINC-3A
34FM12A1QWINC-3Adelete serial number in column B, keep line number 3 in column A
44FM12A1QWINC-4A
44FM12A1QWINC-4Adelete serial number in column B, keep line number 4 in column A
View 9 Replies
View Related
May 5, 2006
I want to write a macro that highlights duplicate values within a single column. I found - on this site a macro that removes duplicate values, which is huge, but sometimes I just need to highlight duplicates values rather than remove them.
View 2 Replies
View Related
Jan 22, 2009
I would like to combine 3 string together but I would like to remove the duplicate found in any of the string. I attached an example with the desired result. I know =A1&""A2&""&A3 can combine the string, as I wan a space between each string. anyone can further edit my formula to remove the duplicate ...
View 13 Replies
View Related
Apr 14, 2013
Is there way, short of creating a macro, to have duplicate rows deleted in a spreadsheet?
View 3 Replies
View Related
Jul 24, 2009
I need to restructure a report that I have imported into Excel.
My columns are titled (from left to right):
Company Name, Account ID, Client Code, Billing Street, Contact Name, Contact Number
There are many rows per Company because every company has many contacts. As a result, for each company, the Company Name, Account ID, Client Code, etc. columns with their associated information in the fields underneath the heading are repeated unnecessarily.
How can I delete the extraneous fields of information (leaving one Company Name field filled out per company) without having to go in an manually deleting them (which would take hours)
View 9 Replies
View Related
Aug 19, 2009
I need some adv on how to remove lines that containing duplicate cells. My data is up the max lines of 65536.
I have data in column D that have duplicate value and I need to remove that duplicate line from the list ( thus leaving only unique value). Is there any macro that can do this?
View 9 Replies
View Related
Mar 6, 2013
I created a spreadsheet for use by the shipping department where I work. We are trying to prevent pulling and shipping the incorrect item to the customer. This is how it works. The employee downloads a CSV file containing items that have been purchased from our website and imports the file into the excel. Then the item is pulled and the employee scans the item's barcode into the spreadsheet named "SCAN." Formulas and code on the "reference" sheet look for the SKU number in the list from the CSV file while others create a consolidated list of SKUs that have been scanned, SKUs that have a problem and need to go to customer service, and SKUs from the CSV file that have yet to be scanned. Some of the formulas in this workbook have been borrowed from forums like this and altered to fit my needs - I'll admit I'm not even entirely sure how they work.
Occasionally a SKU is scanned in twice and not always sequentially which will effect the total item count. It can be very difficult to find the duplicates in the list.
I would like for any value that appears more than once in column B of the 'SCAN' sheet to be highlighted. I have tried to do this, but to no avail.
Scan Below
FWD: CS?
If FWD: CS, state reason below:
Status
NOT SCANNED
FWD to CS
SHIPPED
3
4
149
0113MSU1018
[Code] ......
View 5 Replies
View Related
Dec 13, 2013
I have a challenge related to a excel sheet we are using for personnel planning.
Vertically we have several projects with action items listed row by row in the following format:
- Project 1 xxxxxx
Project management
Project Engineering
Workshop
Shipping
Etc.
- Project 2 xxxxxx
Project management
Project Engineering
Workshop
Shipping
Etc.
Horisontaly on the columns we have dates, day by day for a year +
For each day we chose from a drop down who is assigned to the action item. The problem is when there are 30-40 + projects and each have 20-30 lines of ation items it is hard to get the overview and avoid double entry. I would use conditional formating to do this, but firstly each project is separated by a line containing different formulas and I would not like the conditional formation to change these cells, and at the same time to put this up manually for each row would take a long time since there are 365 + rows.
how a VBA code could solve this little challenge? IT would also be beneficial to have it do the check upon cell change, that way we would not need to manually run the VBA to check.
View 4 Replies
View Related
Jun 26, 2009
In Column "C" there is a list of numbers ( these number realte to incidentsm and are called incident IDs) and I want to delete the rows that have duplicate incident IDs.
the row of numbers looks like this ....
View 9 Replies
View Related
Aug 15, 2008
Let's say I have two mailing lists, A and B.
A:
bob@bob.com
jeff@jeff.com
tom@tom.com
cindy@cindy.com
jose@jose.com
B:
fred@fred.com
jeff@jeff.com
angus@angus.com
cindy@cindy.com
chuck@chuck.com
Now I want to create list C, a modified version of list B, which is made up of everybody in list B, EXCEPT for those people who are also present in list A (in this case, Jeff and Cindy).
So basically list C should look like this:
C:
fred@fred.com
angus@angus.com
chuck@chuck.com
What would be the fastest and most efficient way to create list C?
View 7 Replies
View Related
