How To Remove Single Entries
Dec 9, 2013
I have a worksheet as shown in sheet 1
With I click on the Macro Button I want to display the contents as shown in sheet 2
That means
In sheet 1 there are number of items purchased shown with different rates.
After clicking on the Macro button in Sheet2 I want the following
1. All the single entries purchase should not be displayed
2. All the items purchased 2 or more times but same amount should not be displayed.
3. Only items purchased two or more times should be displayed and that also with different values. Here if the item is purchased 4 times but only once the amount is different then also it should be displayed.
I have given only few items but actual items are more.
The serial numbers should also be arranged automatically starting from 1,2,3, etc.
What is the code if I do not want to change the serial numbers. with a suitable macro.
View 2 Replies
ADVERTISEMENT
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
Mar 7, 2007
Wihitn a large SS I have a column which has a number of entries that may include an amount of duplicate entries. Till now I have been using Sort and them manually deleting the duplicates is anyway that I can select just the Column and then without sorting go down and if a duplicate is found, then the duplicate would be deleted. Meant to say the dta is numeric.
View 2 Replies
View Related
Jul 14, 2013
I have data like this
namefoodripeness
bobbanannaA
boborangeA
bobstrawberyA
bobkiwi B
bobyogurtC
bobpear C
[Code]...
Not sure how to do it
View 3 Replies
View Related
Nov 20, 2012
I have a spreadsheet of over 4000 rows. Column 1 is the part, column 2 is the order #, column 3 is the line # from the purchase order, column 4 is cost. I.E. Part- M12053235, Order #- 580001, Line #- 2, Cost- $563.00
Our software duplicated some of the entries and I need to find a way to remove the redundant entries.
I tried using this formula =if(and(b1=b1:b3909,c1=c1:c3909),"redundant","no")
That formula doesn't work because if it does have matching values in column 2(order #) then it looks to see if column 3(line #) matches anywhere in the spreadsheet. Since the data in this column ranges from 1-30, there is always multiple matches. So, my formula shows a redundant entry even when it is not.
I need to find a way so to check if column 3 has matching values in column 3, but only for the range of cells that corresponds to the purchase order in column 2.
View 4 Replies
View Related
Mar 6, 2008
I have a list in Row A that has duplicates. I want to be able to delete both entries (itself and the duplicate). When done I want the list to display only be entries that are unique or better said any entries that never had a duplicate.
View 8 Replies
View Related
Jan 20, 2008
a way to separate out data that has been all entered into a single column
code .....
The above information is a sample of the data. The data has been entered into 1 column, column A.
I want to separate the entries in to 1 column each for vehicle ID, full journal description, actual journal, EE code, Report total and equipment description.
View 9 Replies
View Related
Mar 6, 2007
I am using old macro code in excel written several years ago. I am trying to educate myself to update and improve the macros I am using but not making much progress. This macro is used to record data from a DDE link to a Data Acquisition System. The D-AQ system sends the data points to r5c3:r5c9 typically. The macro then records the data points in those cells to the next row in sequence on a specific time interval. However, the macro is recording duplicate entries for each set of data points. Sometimes just two, but has been as many as four duplicates. I have a remove duplicates macro to run, after the fact, but preventing them from occurring in the first place is the goal. I included the application.wait method at 10 second intervals after the application .OnTime method, but it isn't preventing the duplicates.
Dim delta_time As Date
Dim Count As Integer
Dim n_rows As Integer
Dim start_row As Integer
Dim col_mov As Integer
Sub initialize_button_set()
start_row = 7
Count = 0
n_rows = Worksheets("sheet1").Cells(1, 2).Value
delta_time = Worksheets("sheet1").Cells(2, 2).Value
Application.OnTime Now + delta_time, "get_data"
Rem Worksheets("sheet1").Buttons.Add 275, 0, 72, 36
Worksheets("sheet1").Buttons.Caption = "STOP"
Worksheets("sheet1").Buttons.OnAction = "Halt"
End Sub...........................
View 2 Replies
View Related
Dec 16, 2008
My check register was exported to a excel spread sheet where the deposits and debits are all in one column E. The debits are entered as a negative number. I want to create two separate debit and deposit columns with the appropriate data entered. What the best way of moving the data accordingly?
View 3 Replies
View Related
Jan 5, 2004
After copying certain data into an excel worksheet, all entries in a column begin at 2nd space of each cell which I don't want.
I can manually backspace each entry to the first space of each cell. But I cannot backspace all entries at once even if I try Find then put in a space for find and then in replace leave blank.
View 9 Replies
View Related
Feb 13, 2007
I regularly have to add a few new lines to what is in fact a very simple data base I've had running for a long time in Excel. About 1200 lines now, one line per person. I add a dozen or so lines (i.e. people) at a time in a different colour.
When I re-sort the whole thing I run my eye down the list to spot partial double (i.e. duplicate) entries (the new ones in their own colour helps). Then I delete the double entries one by one. Pretty stoooopid, in'it?
How can I do this better, faster and more accurately with Excel to find just two duplicated data ( NAME and ID NUMBER) in a person's line (there are 15 columns altogether)? Or: Where and what can I search for (I've just spent nearly an hour trying to find an answer by myself, but don't really know what to search for)?
View 3 Replies
View Related
May 8, 2013
I have a large data set which contains four coloumns: Supplier, Supplier number, order number, and date/time of delivery. The date/time coloumn is formatted as YYYY-MM-DD HH:MM with a 24h time notation. What i want to do is to find deliveries that occurs within 1 hour and that are from the same supplier. So i basically want to group (?) the data with regards to the suppliers and then, within these subsets, check for date/time entries that occurs within 1 hour from each others by "reading" each date entry and compare it to the following one(s) (and maybe stop comparing when the 1 hour interval is passed)?
Furthermore, even if this one might be very hard, it would be good if i could make sure that the entries that are "tagged" as within a 1 hour interval, wont be used as basis for a new interval or be included in other intervals.
The result i am after would be number of 1 hour intervals for each supplier and the number of entries in each interval.
Below is an example from the date/time coloumn:
12-03-08 15:32
12-03-08 15:33 ... Interval with 2 entries
12-03-12 14:54
12-03-28 11:57
12-04-16 09:10
12-05-07 13:41
12-05-07 13:46 ... Interval with 2 entries
12-05-28 11:55
12-05-28 12:00
12-06-04 12:01 ... Interval with 2 entries
12-06-04 12:09
12-06-11 08:30
12-06-11 08:31
12-06-11 08:59 ... Interval with 3 entries
12-07-02 11:10
View 8 Replies
View Related
Jun 2, 2007
I have a column that has duplicate information in it.
An exapmle of one of the cells is:
BL, BL, BN, BOL, BOL, CP, CP, DRE, EG, EG, LM, LM, R, RE, RE, SP, SP, W, W
I want a vba macro to go thru the column and delete the duplicates in each cell.
So this one will be:
BL, BN, BOL, CP, DRE, EG, LM, R, RE, SP, W
View 9 Replies
View Related
Feb 19, 2013
Is there a way to clean all of the single leading quote marks from all cells in a sheet?
'Bob
'Tom
'Jerry
to
Bob
Tom
Jerry
I'm importing data and I have several columns to deal with and will never know how many columns/rows are in a sheet.
View 9 Replies
View Related
Dec 4, 2013
I have scoured the net for the following and have not been able to find a solution. I have found variations but not something specific for the use needed here.
I have attached a file with sample data which has the following format.
Column 1 = vehicle model (in this example golf, jetta, but there will be over 100 choices)
Column 2 = vehicle package option for specified vehicle model (automatic transmission, manual transmission, automatic transmission with air conditioning, etc...)
Column 3 = vehicle colour available for vehicle package option
Please note that Column 2 values for 'golf' are different than values available for 'jetta' (in the sample data I have blocked out common values with the same colour for quick and easy identification)
Also please note that Column 3 values of colour options vary for each 'vehicle package option'.
The behaviour that would be ideal is to have 3 drop down menus. The first drop down menu will allow selection from column 1 and will show each model only one time and repeated values will not be shown. The second drop down menu will only show the options available based on the selection in the first drop down menu. Likewise, the third drop down menu will show the colour options available based on the selection in the second drop down menu.
Since my data will involve hundreds of unique values in column 1 with plenty more added over time, it seems that the format of the data and the way it is laid out in the attached sample is the easiest way to organize it. It is perhaps also the easiest way to include new data without a lot of reprogramming.
Sample Data - Dependent Drop Down Cells Question.xlsx
View 8 Replies
View Related
Oct 9, 2009
I am a SAS programmer and often use SAS Proc Export to dump data to Excel. Sometimes the data is an Excel formula, e.g.
=hyperlink("#Sheet1!r1c1","click here")
Because the data is text, what get's put in the cell is '=HYPERLINK("#Sheet1!r1c1","click here") (note leading single quote).
I cannot use the replace function to edit them out, so must hand edit each one out -tedious at best.
View 4 Replies
View Related
Apr 23, 2013
I'm okay with Excel, but I'm just getting into utilizing VBA and I've been searching high and low for a simple VBA code that will remove/delete rows based off a single criteria without loop as there are over 40,000 rows. I tried a couple that I found onilne, and adapting them to my criteria range, but no luck. (All the ones that I found that work use loop and it takes about 15 mins to run through the entire spreadsheet)
I would like to maintain my first row as it's my headers. My single criteria is to remove all rows that have "NO" in column D.
View 9 Replies
View Related
May 4, 2008
I have to take a range of phone numbers that I have to port from another carrier and manually type them out or copy into another speadsheet, removing duplicate numbers. For example, I have the following numbers:
55532821055553282108
78132821235553282123
55532821625553282163
The results on the next spreadsheet need to be:
5553282105
5553282106
5553282107
5553282108
7813282123
5553282162
5553282163
I am looking for a way to automate this process.
View 8 Replies
View Related
Jan 17, 2014
How can i use a single command button to do multiple task in Excel 2010.
First Instance I click -- It filters only With Record "Yes"
Second Instance I click -- It filters only With Record "No"
Third Instance I click -- It removes filter.
View 3 Replies
View Related
Jan 15, 2013
I am looking to split multiple different entries in a single cell into multiple columns and repeat this for all rows
Example (I have the below in a single Cell as column headers)
NCM Server Mgmt VLAN Site ID
Next Line down is the data (Each row in a single cell)
Enabled 10.10.10.0 50 TEST SITE 1
Enabled 10.10.20.0 50 TEST SITE 2
Disabled 10.10.30.0 50 TEST SITE 3
How I could achieve this as I have a number of projects where this would become useful
I know you can use delimiters but with spaces between the values I just can't fathom a way forward.
View 12 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
Oct 28, 2012
Ok, so lets say i have one list of names (cell:value)
A1:Value 1
A2:Value 2
....
An:Value n
and create a new list
F1:Value 1
F2:Value 2
....
Fn:Value n
I want to have a list nearby and i'd like somehow for a function that will remove entries that already exist on the first list.
View 4 Replies
View Related
Mar 1, 2012
I have a string of text in cell A2. In cell B2 of my spreadsheet is a formula that calculates a number based on the text string in cell A2.
I want to write a VBA loop that removes a single character from the cell A2 string, then calculate the new value in cell B2. I want this loop to continue until the value in B2 falls below a set value (in this case 60).
My code so far
Sub trim_text()
Dim mytext As String
Dim myanswer As Integer
mytext = Range("A2")
myanswer = Range("B2")
Do While myanswer > 60
mytext = (Right(mytext, Len(mytext) - 1))
Loop
End Sub
This obviously does not work. In my excel table I have a formula in cell B2 to calculate "myanswer" will this work, or does that code have to be placed into the VBA code?
View 3 Replies
View Related
May 8, 2006
Find Multiple instances of Numeric Criterion in Row & Return To a Single
Column.
I have a Dynamic Named Range "Data" spanning 10 Columns and many Rows.
Each Row may contain duplicates of the Numeric Criterion.
I would like to find ALL instances of a specific Numeric Criterion across
each single Row in the Dynamic Range "Data" and have the Results returned to
a New Sheet in a single column.
NEW Sheet:
The Numeric Criterion is housed in G5.
The matched criterion should be returned to the New Sheet starting at G7.
Duplicate instances in the same Row should ALL be returned to the same cell
in Column G on the New Sheet.
Sample Data Layout:
Columns I J K L M N O P Q R
Row No.76 1 0 1 1 0 1 1 1 0 1
Row No.77 2 2 3 2 1 2 2 0 0 0
Row No.78 3 3 3 3 3 0 3 0 3 0
Scenario:
Looking for Numeric Criterion 1 (one).
Expected Results - New Sheet:
Row No.7 Column G (Cell G7) 1111111
Row No.8 Column G (Cell G8) 1
In Row 76 of the Sample Data ALL seven Numeric Criterion of 1 (one) should be
returned to the same cell G7.
In Row 77 of the Sample Data there is only one Numeric Criterion of 1 and it
should be returned to cell G8.
View 14 Replies
View Related
Aug 8, 2009
I have found a very useful UDF for removing non-alpha characters from strings. (See below, Credit for posting to Stanley D Grom - Ozgrid post ´Removing Non-alpha Characters From Text´).
Option Explicit
Private Function RemoveCharacters(InString As String) As String
Dim intLoopCounter As Integer
Dim intStringLength As Integer
Dim intASCIIVal As Integer
intStringLength = Len(InString)
InString = LCase(InString)
For intLoopCounter = 1 To intStringLength
intASCIIVal = Asc(Mid(InString, intLoopCounter, 1))
If intASCIIVal >= 97 And intASCIIVal <= 122 Then
RemoveCharacters = RemoveCharacters + Mid(InString, intLoopCounter, 1)
End If
Next intLoopCounter
End Function
Two requests:
1. Could the UDF be modified such that any part of a string contained within brackets is also removed (e.g. "NLGA High Street (West-Enfield), EN6" becomes "nlgahighstreeten")?
2. Can an argument be added to the format of the UDF, such that numbers (0 to 9) are either included or excluded (e.g. RemoveCharacters(A1,1) where the argument ´1´ would include any numbers (0 to 9), so "NLGA2003 High Street (West-Enfield), EN6" becomes "nlga2003highstreeten6")? ´blank´or ´0´would exclude these numbers, i.e. would return "nlgahighstreeten"
View 5 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
Mar 21, 2008
I have a text file containing internet explorer browser history. The file has data in the following format (in Excel all data is in 1 column): ...
View 9 Replies
View Related
Jan 7, 2009
If I have, in one cell (call it D1):
EH,DR,HU
and in a lookup table on another sheet:
A B
1 ED T
2 EH F
3 DR G
4 HU H
5 SE E
6 YU E
I need to be able to lookup the values in D1 on the table and return the values in column B to a single cell (say E1), also comma separated...
eg...
F,G,H
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
