Sort Row Based On Duplicate Value
Mar 15, 2014
I want to sort row based on duplicate value in Column 2 & 3. And copy the content if Column 3 is Self-duplicate.
Hard to explain, something like this:
Column 1
131.74
1.63
12.31
Column 2
375-9323
384-803
267-6491
Column 3
375-9323
NO
384-803
267-6491
375-9323
Result should be like this:
Column 1
131.74
1.63
12.31
131.74
Column 2
375-9323
384-803
267-6491
375-9323
Column 3
375-9323
NO
384-803
267-6491
375-9323
I've been playing with different Macro and search quite a few places but haven't got any luck so far.
View 2 Replies
ADVERTISEMENT
Jul 18, 2013
I have data and i would like to number duplicates next to the data so that i can append the cells that are duplicates easily. The data i have is in the following cell format -
Red
Red
Red
Green
Green
Green
Green
Blue
Yellow
Yellow
What i would like is to output serial next to the cells based on the cell uniqueness as in -
Red 1
Red 2
Red 3
Green 1
Green 2
Green 3
Green 4
Blue 1
Yellow 1
Yellow 2
View 4 Replies
View Related
May 26, 2009
in the attached WB you'll find a Source Table that should be duplicated and in the same time sorted as per the Points. The task should be accomplished with NO VBA, NO Helper Columns and NO Manually Copy+Sort.
Column "H" formula was no problem but I'm stuck with the "G" formulas as you can see in cells G5 and G7. I found it difficult to handle two different names with the same points. What would be the correct formula for column "G" ?
View 2 Replies
View Related
Feb 26, 2014
I need to sort my data by the oldest date first in colum L (NEED_DATE), but I need to keep the rows grouped by colum A (Material No). See example of data.
Material No
Tool No
Prodn Ordr No
Curr Oper No
Curr Workcenter Cd
Next Oper No
Next Workcenter Cd
[code]....
View 2 Replies
View Related
Jul 4, 2014
I want some example of VBA that could copy data (A1:B15) to new range (D1:E15) then sort and remove duplicate.
SWS
Central Pinklao FLG
RMA Trading
Thonglor 13
SWS
IT Square
[Code] ......
View 1 Replies
View Related
Feb 17, 2011
I need a quick macro to duplicate based on specific cell value in column D. If the value is 3, then I need the line to be copied three times and then change the original value line to an incrementing value.
Starting Data:
A_______|B_____|C_____|D_
CompanyA FacilityB Group1 qty3
CompanyA FacilityB Group2 qty2
CompanyA FacilityB Group3 qty4
CompanyA FacilityB Group4 qty1
CompanyA FacilityB Group5 qty5
End Result:
A_______|B_____|C_____|D_
CompanyA FacilityB Group1 001
CompanyA FacilityB Group1 002
CompanyA FacilityB Group1 003
CompanyA FacilityB Group2 001
CompanyA FacilityB Group2 002
CompanyA FacilityB Group3 001
CompanyA FacilityB Group3 002
CompanyA FacilityB Group3 003
CompanyA FacilityB Group3 004
CompanyA FacilityB Group4 001
CompanyA FacilityB Group5 001
CompanyA FacilityB Group5 002
CompanyA FacilityB Group5 003
CompanyA FacilityB Group5 004
CompanyA FacilityB Group5 005
View 5 Replies
View Related
Aug 8, 2007
I am using three columns - Col A contains serial number, col B contains name and Col C contains Codes.
The problem is in col C as sometimes the codes are copied from other files also. So I want that if there is any duplicate codes in column C, on running the macro it will delete the duplicate rows and keep original record ie the duplicte records added later has to be deleted and that the original record will be in place.
View 9 Replies
View Related
Jul 30, 2008
I got some functions from some of you to spot duplicate bar codes
the first function was this =IF(ISERROR(MATCH(D5;$D6:D$65536;0));"";D5)
the second next to it this =COUNTIF($D:$D;$D5)
and the third: =IF(COUNTIF(D:D;D5)>1;"DUPLICATE";"")
well fine but now I dlike to add the numbers of the 2 cells of those duplicate codes. In some cases I have more than 2 identical bar codes.
Example
4905524490831DPFV900B.CEU0649055244908312DUPLICATE
4905524490831DPFV900B.CEU502DUPLICATE
View 9 Replies
View Related
Feb 18, 2010
I need to filter the following file.
I need to remove all rows where COL A value and COL B value are the same. COL C does not need to be considered. However I need to retain one of the Col C values for purposes of formatting.
The end result should look similar to columns F,G and H!
View 2 Replies
View Related
May 22, 2014
I have a large spreadsheet that has some duplicates in Row Q. However, in column L there is a date. I want to be able to find the duplicates in column Q then delete the duplicate with the earliest date.
[Code] .....
View 4 Replies
View Related
Feb 6, 2010
I have a worksheet with two columns and a few hundred lines. One has titles and the other integers indicating how many times the title needs to be copied to another worksheet or text file.
Existing Worksheet (Input)
TitleA1
TitleB6
TitleC4
TitleD3
Desired Output (Worksheet or Text File) [The entire row, including the integer could be copied as well, if it is easier.]
TitleA
TitleB
TitleB
TitleB
TitleB
TitleB
TitleB
TitleC
TitleC
TitleC
TitleC
TitleD
TitleD
TitleD
View 7 Replies
View Related
Feb 6, 2010
I need a VBA/macro very similar to several I have seen posted here. I have a worksheet with two columns and a few hundred lines. One has titles and the other integers indicating how many times the title needs to be copied to another worksheet or text file.
Existing Worksheet (Input)
TitleA1
TitleB6
TitleC4
TitleD3
Desired Output (Worksheet or Text File) [The entire row, including the integer could be copied as well, if it is easier.]
TitleA
TitleB
TitleB
TitleB
TitleB
TitleB
TitleB..............
View 2 Replies
View Related
Oct 10, 2011
Currently trying to solve a niggling issue with removing duplicate row data from competition entry databases using the following code...
Code:
Sub remove_duplicates()
Dim lastrow As Long
Dim i As Long, j As Long
On Error GoTo errhandler
Columns("A:G").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
[Code] ......
errhandler:
'MsgBox Err.Description
End Sub
What I need to happen is for duplicates to be based on both the name and email matching (columns A + B) and then remove all duplicate rows, whilst ignoring but preserving the content in columns C,D,E and F on those rows that remain. Column G contains either "Yes" or "No" depending on a users membership status. Sometimes duplicated data will contain identical name and email address but have different membership status in column G where someone was logged in when submitting a form the first time, and then logged out the next (potentially a mixture of the two over multiple times). Therefore I need any duplicate users who have different values in the G column throughout the database to be detected by the macro and we must ensure that the single row retained in the final non-duplicated list displays "Yes" in column G for that user.
Duplicate users who are always "yes" or always "no" work fine, its only the ones with mixed values for column G that need to be analysed and their return forced as Yes in the final list.
The final list should preserve the original column structure and thus be only one row per unique user.
The above code appeared to work the first time with a sample file, but when running the macro on one of my full size lists (3k - 5k rows) it returns 2 rows for each user who started off as a mixture of Yes/No... ?
Both rows will say yes in column G (so that part is correct!) but I have to re-run the macro again on the same dataset for it to remove the duplicate yes rows to get the list as it finally should be!
So I can achieve the result by running it twice, but this is a bit of a cheating way to do it and also the next stage after this involves something similar across multiple worksheets so I need something rock solid to build upon!
View 5 Replies
View Related
Nov 3, 2011
a quick macro/VBA project that would allow my to duplicate existing rows based on the count in one of the cells. For example, my data currently looks like this:
DCITEMITEMDESCVENDORCOST ABCSTATUSRCATOHExtended Cost 1st Discount Level 2nd Discount Level CGO6-IAVHORN12/24VDC MINI HORN07697$ 12.69 UA24901$ 12.69 $ 9.99 $ 5.99 CGBK-PR2AEPOXY-ENCAPSULATED RELAY W/ACT20775$ 7.43 SA25203$ 22.29 $ 5.99 $ 3.99 CGSF-SUB901CLEAR 2"SPACER F/STI 910507450$ 7.88 SA25904$ 31.51 $ 5.99 $ 3.99 CG0Q-BLTBWIR21OUT B/W IR BULLET 420TV 3.6M01047$ 66.59 XD31211$ 66.59 $ 49.99 $ 32.99
And I want to duplicate the rows based on the quanity in the OH column (highlighted in yellow) - so that it looks like this:
DCITEMITEMDESCVENDORCOST ABCSTATUSRCATOHExtended Cost 1st Discount Level 2nd Discount Level CGO6-IAVHORN12/24VDC MINI HORN07697$ 12.69 UA24901$ 12.69 $ 9.99 $ 5.99 CGBK-PR2AEPOXY-ENCAPSULATED RELAY W/ACT20775$ 7.43 SA25203$ 22.29 $ 5.99 $ 3.99
[Code] ........
View 3 Replies
View Related
Feb 23, 2012
I have a set of data I'm trying to clean up. I'd like some code that would look at column A, and if a duplicate is found in A, delete the duplicates entire row. Column A contains a unique identifier code.
View 9 Replies
View Related
May 1, 2012
I have a sheet which has the following Columns:
Name..........Sales..........Returns..........Net
These are columns B,C,D,E,
Because the data is imported, it shows one row for the person's sales and another for their returns like this:
Dave...........100...............0...............(Blank)
Dave..............0.............-20..............(Blank)
Fred............200...............0...............(Blank)
Andy..............0............-500..............(Blank)
What I am trying to do is get the net sales of 80 in either of Dave's rows in the Net column, then I can hide the duplicate row. However, this is complicated by the fact that names without returns, (like Fred in this example,) do not have a second row. (This also applies to those names without sales, but with returns, like Andy).
View 6 Replies
View Related
Nov 8, 2012
I just want to colour duplicate values but want to do it with this Dictionary method
Code:
Sub highlight_Dups()
Dim cell As Range
Dim rng As Range
Dim dict As Dictionary
[Code]....
View 9 Replies
View Related
Mar 12, 2013
Every week I get a report that contains 1 or more (sub) part numbers in each row down column E, separated by a space. I need each part on a separate line, with the rest of the data on that row intact. Because these are the sub-components of an overall part, the data in column E is stored grouped together like that.
I currently use text to columns to break those up, separating them by the space, add a count of those cells so I know how many sub part numbers there are, run a 'duplicate rows' macro on that count, then manually delete out numbers so I'm left with one part number per row. The report is already over 100 rows long, so after I run the 'duplicate rows' macro I have a lot of rows to work through.
View 7 Replies
View Related
Aug 29, 2008
I have data contained in 4 columns. Column A is name, Column B is ID, Column C is Company, Column D is amount. There are duplicate names in column A and duplicate ID's in column B (ID's have correspending Company Names in Column C). I want to have a list so that this combination shows up only once, and then the amount next to it will be the sum of all.
Example:
DataABC DTom9730-003Company B100 Joe10242-001Company A200 Tom9730-003Company B300 Joe10242-001Company A400 Sam9730-003Company B100 Desired ResultTom9730-003Company B400 Joe10242-001Company A600 Sam9730-003Company B100
View 9 Replies
View Related
Jan 7, 2010
How do I delete duplicate rows in a sheet using a macro. When I say duplicate row, it is not based on a particular column but all the columns, so it is a true duplicate record.
View 9 Replies
View Related
Aug 14, 2007
I have a problem of having to repeatedly copy and paste a series of data based on the number of occurrences in each row of data series. For instance:
[Road Name] [Building Name] [No. of occurrence]
(1) [Scotts Rd] [Tangs Building] [38]
(2) [Orchard Rd] [ABC Building] [3]
(3) [French Rd] [DB Bank] [1]
I will need to duplicate (1) 38 times with the location name and building name. Similarly, I need to duplicate (2) 3 times. How can I simplify it programmatically?
View 3 Replies
View Related
Aug 23, 2007
I need VBA help. I know how to highlight every other row but what I can't do is highlight a row if column values are the same. For example I have 5 columns but only the first one has the data I'm interested in. If the data in the first column looks something like this:
1
2
3
4
4
5
5
5
6
6
6
7
.
.
I want to highlight every other row that has similar values therefore I want to highlight row 2, both row 4s, and three 6s...etc.
I also want it to start on the 2nd row of every page until the last row of every page which is row 37 in my case.
View 9 Replies
View Related
Mar 1, 2008
I have a number of rows that I want to have duplicated X number of times (and altered) where X is found by looking at certain cells within each row.
There are four numbers in each row, and I want to split them up into multiple rows each with three zeros and one one.
I would like to convert data from this:
Name W X Y Z
John 1 0 0 0
Doug 0 0 1 0
Karl 3 0 1 0
Mike 0 1 1 2
etc.
...to this:
Name W X Y Z
John 1 0 0 0
Doug 0 0 1 0
Karl 1 0 0 0
Karl 1 0 0 0
Karl 1 0 0 0
Karl 0 0 1 0
Mike 0 1 0 0
Mike 0 0 1 0
Mike 0 0 0 1
Mike 0 0 0 1
etc.
You can see that the W, X, Y, and Z columns from the four new Mike rows sum to equal the values in the original Mike row (0, 1, 1, 2), but everything has been split so that each row just has a single one in it and three zeros.
Does anyone have an idea of how to do this? Thanks Auto Merged Post Until 24 Hrs Passes;I thought of another way of putting it that may be easier to understand.
Given an input row of "George, 4, 7, 3, 2", I would like the output to contain 4 rows of "George, 1, 0, 0, 0", 7 rows of "George, 0, 1, 0, 0", 3 rows of "George, 0, 0, 1, 0", and 2 rows of "George, 0, 0, 0, 1".
View 3 Replies
View Related
Jun 12, 2008
creating a macro for an excel spreadsheet? The problem I am faced with is I need to reduce a xls file from a ticketing system at work that contains roughly 50,000 rows.
What I need to have done is reduce a Site Code column (column B) so that for each ticket entry there is only one occurrence of each user that worked on that ticket.What the macro should do is to look through column B and remove any duplicate rows for the same ticket number located in column A. once the macro gets to the next ticket number in column A I need it to start over on the duplicate check for column B.
I have a attached a xls file to this post to try and show what it is i am working on.
View 8 Replies
View Related
Jul 23, 2014
I have a list of people that I want to sort according to their age in ascending order. If several people have the same last name (family members), I want those to be sorted together. After clicking on the SORT-button the result should look like this:
[Code] ...
Attached File : Test2.xlsm
View 13 Replies
View Related
Feb 16, 2010
I have a data set that id like to sort via macro. The first two fields are not relevant to the sorting proces.(Column A & B ). Column C contains totals some are zero, some are greater than zero and some are less than.zero. I have a macro which I use for many reports that sorts based on Autofilter but I dont know how to change it so that I can use less than, greater than and = to 0. Below is my attempt which didnt work.
View 9 Replies
View Related
Jun 4, 2008
sorry for restarting this thread but i started one similar on saturday and the person I was talking to has not yet responded
I want to know if it is possible to adjust this code line:
Case "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"
from the macro below to instead get the case names from a list on a worksheet... the reason for this is that the names of the worksheets I want sorted will change periodically.
Sub SortDaysoftheWeek()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Name
Case "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"
With ws
.Activate
.Range("A6:G256").Sort Key1:=Range("A6"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Select
Next ws
End Sub
View 9 Replies
View Related
Jun 26, 2008
I'm looking to sort one column based on the values of another.
For example, if Column A has a list of names, and column B has a list of their ages, I just want to sort Column A based on the ages, but I want the ages column to stay as it is.
Is it possible to do this without a Macro? And if it requires a macro, how can I write a macro to make this work?
View 3 Replies
View Related
Nov 28, 2013
I have a sheet with 45,000 rows. Let's say each row has 4 columns: Create_timestamp, Update_timestamp, email_address, and o_flag
Many rows have duplicate email addresses. I would like to remove all the duplicate rows, EXCEPT for the row with the most recent Update_timestamp.
And actually, if I could just "hide" all those rows, that would be even better, but I'd be happy just figuring out how to delete all the "old" rows, so I just have a list of unique email addresses, with their create/update timestamps and o_flag column. Seems like this is such a basic use case for "Remove Duplicates,",.
View 1 Replies
View Related
Jun 18, 2009
I need to concatenate and de-dupe. I want to find dupes, concatenate a column, and the delete the duplicate columns--all while maintaining data from other populated columns.
Here is the some example data:
#| Type | Name
1 | A | Bob
1 | B | Bob
1 | C | Bob
2 | B | Sally
2 | E | Sally
3 | A | Bill
3 | C | Bill
4 | E | Nancy
The result should look like:
# | Type | Name
1 | A, B, C | Bob
2 | B, E | Sally
3 | A, C | Bill
4 | E | Nancy
I am using Excel 2003.
View 5 Replies
View Related
