Moving Data From Rows To Columns And Delete Repeated Rows
Apr 16, 2014
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
View 5 Replies
ADVERTISEMENT
Sep 24, 2009
I have the following spreadsheet with 4 columns of data:
1000001245aMr Fred Blogs
1000001270a1 Blog Street
1000001270bNorwich
1000001270cNorfolk
1000001270eNRx x99
1000001270gThe Manager
1000001270k01603 555 555
1000001270l01603 555 555
1000001270qManager
1000001270xN
1000001270yN
1000002245aMrs Fredilina blogs
1000002270a10 Blog Street
1000002270bNorwich
etc etc for about 36,000 rows.
What I need is for each unigue reference ID (column A),
I need all corresponding data moved up into a single row in serperate columns, something like this:
1000001 |245|a|Mr Fred Blogs| 270|a| 1 Blog Street|270|b| Norwich | 270|b|Norfolk |etc
1000002 |245 |a |Mrs Fredina Blogs |270|a|10 Blog Street |270|b|Norwich| etc
Each entry doesn't nessesarily have the same number of rows.
I would class myself as a beginner VBA bod, but the solution to this one is illuding me a present.
View 6 Replies
View Related
Sep 14, 2009
how to delete repeated rows and just keep the first one found?
View 13 Replies
View Related
Dec 12, 2008
I have address data all in column A (each paret of the address is on a separate row) which I need to move to separate columns on one row for each address:
ie:
Mr Bloggs (row 1)
123 The Avenue (row2)
The town (row 3)
The postcode (row 4)
Mrs Smith (row 6)
456 The street (row7)
The postcode (row 8)
needs to be: (I have used || to indicate different columns)
Mr Bloggs || 123 The avenue || The town || The postcode
Mrs Smith || 456..... || .......
The problem you see from the above is that they are not always consistent ie. the postcode (zip) isn't always 3 rows below the name for example. Also although generally there is a blank row between each record (this may sometimes be 2) and equally there may also be times when there is a blank line in the address.
Any ideas on how to do this.... I have 1000's of records and really can't afford to go through each one making it consistent before moving it to columns?
View 7 Replies
View Related
Nov 26, 2013
import it into to Stata to do statistical analysis. I always receive spreadsheets like this:
country
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
[code].....
and I have to manually reorganize it like this to import into Stata:
country
year
value
Benin
1991
20
Benin
1992
254
[code].....
Is there way I can quickly design a macro to do this? The problem is that I generally have a list of about 60 countries, and years from 1991-2011. So, it's really time consuming copying the column of data corresponding to the year, pasting below, repasting the list of countries and the years...then again..then again...then again...I'm using Excel 2010.
View 7 Replies
View Related
Jan 18, 2008
I have a excel doc with 8000 names I need to delete the people who are listed on 7 rows.
There are no blank rows and Colume A has the names sorted.
View 9 Replies
View Related
Jun 24, 2014
I have a worksheet which contains 2 columns which is needed to work my problem.
Unique Work ID and Description
The unique work ID are the same for each description, but there are up to 5 different description associated with each unique work id.
I'm looking for an automated process but where to start to convert the 5 rows in the unique row and 5 column for the descriptions
View 8 Replies
View Related
Sep 21, 2012
two things on the attached sheet.
First problem: in column B, I have a formula based on contents of column A. However, the limitations of 7 nested IFs leaves me with errors - I need 3 more IFs. Is there a way around that?
Second problem: I need to show the row of the first (earliest time in column A) and the last (latest hour in column A) record for each name. I've been sorting by name and time, then manually deleteing the rows I don't need. I know there's a better way, but I can't find it!
View 6 Replies
View Related
Mar 18, 2014
I have a spreadsheet with hundreds of rows. Columns C and D contain either TRUE or FALSE. I want to be able to automatically delete the rows where both columns are FALSE.
View 1 Replies
View Related
Mar 14, 2008
I'm trying to come up with a formula to find the last row without any data in columns E and F and delete the 10 rows below them. Is there a way to do this?
View 3 Replies
View Related
Jul 13, 2009
I am trying to move row data into column data. Here is visually what I need.
Before:
After (one row of data only):
We are trying to import data into our accounting software but we can't until we get it into the right format.
View 9 Replies
View Related
Jun 5, 2009
I have a massive data file with thousands of lines of data. Each line is a pair, for example
View 6 Replies
View Related
Jul 29, 2009
I am running through some old accounting documents in excel 2003 (originally from SAP), that contain information in row form that I would like in Column form. Column E indicates to me whether this was an invoiced amount or payment amount or "other." I have limited VBA exposure, but am fairly proficient at what I do know. This is just too difficult for me!
Where the "Assignment" #'s are equal, I need the script/macro to move "up" YP's, YQ's,
See Original Layout.jpg
Then What I'd Like.jpg
Then End Result
View 9 Replies
View Related
Nov 2, 2011
I have following data on spreadsheet, which I need to import into other place but I would need to reorganise rows and columns first.
Product1-Sep2-Sep3-Sep4-Sep
SKU1112345
SKU22212131415
SKU3339876
SKU4444444
Basically I would like to have this data in 3 columns like this:
DateSKUOrders
1-SepSKU1112
1-SepSKU22212
1-SepSKU3339
1-SepSKU4444
2-SepSKU1113
2-SepSKU22213
2-SepSKU3338
2-SepSKU4444
3-SepSKU1114
3-SepSKU22214
3-SepSKU3337
3-SepSKU4444
4-SepSKU1115
4-SepSKU22215
4-SepSKU3336
4-SepSKU4444
I tried transpose etc. and not able to figure it out. Is this something that can only be accomplished with VBA?
View 2 Replies
View Related
Jul 28, 2008
to prepare an excel spreadsheet for a mailmerge but as all of the info for 1 recipient needs to be in columns instead of rows. I need to convert 2 columns' data into columns but only when there are duplicate invoices, see below;
View 9 Replies
View Related
Jun 16, 2008
Will excel allow text to be permanently be positioned in a specific cell even if rows or columns are added?
View 6 Replies
View Related
Oct 24, 2012
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB:
Public Sub DelRows2()
Dim Cel As Range, searchStr, FirstCell As String
Dim searchRange As Range, DeleteRange As Range
[Code].....
View 1 Replies
View Related
Jan 28, 2014
I need to build a macro which copies 3 rows every day and pastes the row data into an identical sheet. The three rows will have column "D" as =today(). As the days progress the three rows will change accordingly ( tag to the today's date)
e.g. 28/1/2014
28/1/2014
28/1/2014
I need the macro to recognize the date when pressed and copy the corresponding rows of data and paste them into an identical sheet with the same date. The second sheet is an archive sheet. The date will tick over as per the calendar.
View 9 Replies
View Related
May 20, 2014
I'm essentially getting a lot of data at the moment, which has a few orders people have made on my website.
Essentially, think order id, address etc and then all the products the customer has ordered.
However, the part which includes what the customer has ordered creates multiple rows of data, with the order ids etc duplicated. What I need to do is consolidate this into 1 row. So to add additional columns instead of rows.
The reason fro this is I want to mail merge the data into an invoice and mail merges work of 1 line of data at a time. I've attached an example, any way to do this?
(Attached to this post / or linked here: [URL] ....)
View 1 Replies
View Related
Jul 26, 2009
I have created a spreadsheet with 4820 rows of data (4821 for computational purposes, the last row being blank) with 5 columns.
1. every third row is blank and it needs to be removed (3, 6, 9,...4818, 4821). Once this is accomplished,
2. I need to move the data that is now found in the even rows, column E moved to the above, current odd row, column F. (ie. E2 to F1, E4 to F3, E6 to F5,...E4808 to F4807...,E4820 to F4819 (however it won't go this high). then
3. I need to delete all the current even rows, as the data I need will be in the odd rows.
View 4 Replies
View Related
Feb 3, 2010
I have an excel file that contains about 1000 rows of data, from column A to O. Column C contains either the letter A or the letter I, A means Active, I means Inactive.
What I'd like to do is replace my monthly manual task of moving all the I's to sheet2. When completed, the excel file should have two sheets, all of the A's on one, and the I's in the other. The original excel file is not sorted by column C. The end result should have the same row 1, being the header row. There are some additional steps, to save the file to a specific location but I think I could do that once the excel file is formatted the way I wanted it.
View 4 Replies
View Related
Jan 9, 2008
I have data that is all in column A there are 8 pieces of information.
So client 1 info is listed from A2-A9, client 2 from A10-A17 etc. etc.
I set up A1-H1 to be the headers necessary.
How do I go about moving A2-A9 to A2-H2, then A10-A17 to A3-H3 etc. down through all the contacts which go down to A2400...
View 9 Replies
View Related
Jul 15, 2009
1. Remove J,K,N,A Columns,
2. In the last O (TIMESTAMP) column, the date is 14-Jul-09 format change it to 07/14/2009 (this format mm/dd/yyy
3.Filter L column (VAL_INLAKH) Remove all rows from whole sheet which has 0 value
4. Column C (EXPIRY_DT) date format is 24-Sep-09 , "dd-Sep-09" change to "Sep" only
5.Merge Column B,C,D,E (SYMBOL.EXPIRY_DT.STRIKE_PR.OPTION_TYP
respectively )
View 3 Replies
View Related
Jun 29, 2014
Any macro capable of moving data from multiple row to a single one i have attach a sample file before and after ...
View 4 Replies
View Related
Jan 7, 2010
This is similar to a previous post, in July, which was masterfully solved. I have tried to adapt the previous script, without success. Therefore,
I have copied data from a screen and entered it into Excel 2007, which is attached as 'snohomish sample.xlsx The data starts out (sheet 1) in 8 columns (A-H), and only in the odd rows (1, 3, 5, ...) What I wish to do is the following:
Move 'sheet 1 column B' to 'sheet 2 column A'. This is a date which needs to be in mm/dd/yyyy form.
Move the last 14 digits in 'sheet 1 column F' to 'sheet 2 column B'. This needs to be text so that I don't loose any of the zeros.
Move 'sheet 1 column E' to 'sheet 2 column C'.
Leave 'sheet 2 column D' blank as I will be entering a currency amount after running the module.
Move 'sheet 1 column A' to 'sheet 2 column E'. This also needs to be text as I can't loose any zeros or have it in scientific format.
The rest of the data on sheet 1 is not needed on sheet 2.
I will be entering data in 'sheet 2 columns F-K' after running the module.
I am attaching 'snohomish results.xlsx' which shows what I hope the results to look like (note the sample results are on sheet 1 of a different file).
View 6 Replies
View Related
Sep 23, 2013
I have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.
View 5 Replies
View Related
Jun 10, 2013
I'm trying to create a .cvs file from an exported excel report, but before I can import it to a database I'd like to have one row per record.
I've already omitted out the headers and footers blank spaces and unnecessary columns from the excel file and I needed to just combine multiple rows into one.
It currently looks like this:
ROW#(1), ID#(1), NAME(1)
ADDRESS(1), ZIP(1)
DOB(1), SOURCE(1)
ITEM(1), CITY(1)
ROW#(2), ID#(2), NAME(2)
ADDRESS(2), ZIP(2)
DOB(2), SOURCE(2)
ITEM(2), CITY(2)
and so on..
I'd like to make it like
ROW#(1), ID#(1), NAME(1), ADDRESS(1), ZIP(1), DOB(1), SOURCE(1), ITEM(1), CITY(1)
ROW#(2), ID#(2), NAME(2), ADDRESS(2), ZIP(2), DOB(2), SOURCE(2), ITEM(2), CITY(2)
and so on...
I can't figure out a macro or a way to do this automatically.
View 8 Replies
View Related
Oct 3, 2012
I am using Excel 2003.
I have 2 worksheets.
Worksheet 1 is called "Master List Data". Every cell within this worksheet contains a formula so that it matches the cell value contained in a worksheet held within another workbook.
The formula for reference is as follows:
=IF('[CCL Breakdown.xls]MASTER LIST - Active Customers '!A1="","",'[WFX CCL Breakdown.xls]MASTER LIST - Active Customers '!A1) .
e.g.
If Cell A1 on Master List-Active Customer contains no data, Cell A1 in Master List Data would be blank.
If Cell A1 on Master List-Active Customer equals John Smith, Cell A1 in Master List Data would display John Smith.
There are currently non-blank values contained in cells A4:A750. But next week there may be non-blank values is cells A4:A790 (i.e. it will grow each week)
Worksheet 2 is called "Master List Flat. At the moment, I am manually copying and pasting the rows which have non-blank values in cells from Column A from Worksheet 1 into this report (e.g. A4:IV4).
I would like to automate this process and I have created a Macro, but I do not know how to word it so that it will only copy cells with actual data in.
The Macro I have written is below:
Sub IMPORTANDFLATTENDATA()
'
' IMPORTANDFLATTENDATA Macro
' Macro recorded 01/10/2012 by walesb
'
'
Application.ScreenUpdating = False
Sheets("Master List Flat").Select
Rows("4:759").Select
[Code]....
View 1 Replies
View Related
Mar 29, 2014
I am attempting to delete rows with columns not containing "MC". In the example below, the result should be 3 rows remaining (1,2 and 5):
Y
Z
AA
IP/DA/SV
MC/SWL/SA
MC/SH/SA
[code]...
Instead, all I get are results with Y starting in MC, in this case the last row, all the other rows are deleted. I need to get all rows that have MC in three columns (Y, Z or AA) and delete the ones that do not. In this case, rows 3 & 4.
Below is the code I have been using:
Sub Del_Rows()
Application.ScreenUpdating = False
With Range("Y1:AA1", Range("Y" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:="*MC*"
.Offset(1).EntireRow.Delete
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub
Seems simple enough but this newbie chump is stumped why it is not reading the other columns
View 4 Replies
View Related
Jan 12, 2007
I tried to modify this code for my next problem: to delete entire columns where the cell in row 2 is empty, but it doesnt work. The code I use is:
Sub test2()
'This code will create a copy of the current sheet, and retain only the rows
'that have the value '1' in column B.
ActiveSheet.Copy Before:=ActiveSheet
Application. ScreenUpdating = False
On Error Resume Next
Dim ir As Long, mrows As Long, lastcell As Range
Set lastcell = Cells.SpecialCells(xlLastCell)
mrows = lastcell.Row
'Note rows are deleted from the bottom going up
For ir = mrows To 1 Step -1
If Len(Trim(Range("b" & ir).Value)) = 0 Then
Rows(ir).Delete Shift:=xlUp
End If
Next
Application.ScreenUpdating = True
End Sub
The ideal solution would be not a second macro but one that does firstly delete the rows and then the columns.
View 8 Replies
View Related
