Moving Data (Addresses) From Rows To Columns

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


ADVERTISEMENT

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 View Related

Moving Data From Columns To Rows

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

Excel 2010 :: Moving Data Organized By Columns To Rows?

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

Moving Data From Rows To Columns - Depending On Unique Content In A Field In Column

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

Moving Rows Into Columns

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

Moving Rows And Columns With A Macro

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

Moving Rows Up Into Columns Where Value Is Equal To

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

Stuck Moving Columns To Rows - Transpose

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

Moving Duplicate Rows To Columns For Mail Merge

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

Lock Cell From Moving When Adding/Deleting Rows Or Columns

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

Moving Data From Two Columns Into Multiple Columns?

Feb 24, 2013

I have a large database of research data where the first column contains different categories (labelled as I, II, III, IV, V, and VI). For each row there is a data value in the second column that is numeric. What I need to do is move the data so that the data are displayed in 6 columns with the data values listed each column heading according to the category label. In the example I've shown there are 6 different categories, but the number of categories (and hence the number of columns in the final resulting sheet) will change for different data sets that I'm using. Also note the the number of cases for each different category is different (so that each column will not be even in terms of the number of rows of data under each column heading). I hope I've been able to explain what I'm looking for clearly.

I've attached a sample file showing the sample data input that I have on one sheet, and then the desired re-tablulated outcome on the second. I have done this using the auto-filter function and copy/paste, but this will take far too long for the larger datasets I'm working with.

View 2 Replies View Related

Moving Columns So That The Data Matches

Jul 21, 2009

I need to line XYZ to XYZ as well as the information attached to XY&Z
i dont really know how else to explain it but i was hoping there was a macro or something out there that i could use to do this would make it a lot easier

View 8 Replies View Related

Moving Data From Several Columns To Tabs

Jul 24, 2007

i have data that has several bits of information for each different account number.
is there a way of writing a macro to transfer the information from the main sheet onto different sheets automatically titled for each of the different account numbers?

ie. Account Cost
E1 £100
E1 £200
E2
E2
etc

i want different sheets for the info next to each of the account numbers

View 9 Replies View Related

Moving And Deleting Entire Rows Between Tabs In Workbook And Moving Them Back If Needed

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

Moving Rows Of Data Into 1 Row

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

Deleting Rows And Moving Data..

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

Moving Rows Of Data To Another Sheet With Vba

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

Moving Data From Column A To Rows

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

Moving Data From Single To Multiple Columns And Merging?

Dec 16, 2012

Current Data:
File 1:

Each set of data is listed in either two or three rows

Eg.,
ID
Date
Filename

ID
Date

ID
Date
Filename

The goal is to move them to separate columns (rows can be 3 or 2 for each data set, and may or may not be separated by space/additional row)

File 2:

Has a common field 'ID' as that of File1, does not have Date, and Filename, but has a new field 'Detail' (already in the expected format)

Eg.

ID Detail

The goal is to merge properly formatted data from File 1 to File 2

Eg

ID Date Filename Detail

View 14 Replies View Related

Searching Columns For A Month Then Moving Data To Userform

Mar 17, 2014

I have an excel spreadsheet where the columns have the 12 months of the year in Row 1. What I need is code that will find the month (from the column) which I select (via a listbox in a userform) and then go down that column and move the data that corresponds to that month to the userform.

So, If I select January from my userform, all data below January will then be moved to the userform and the same thing will occur with each month I select. I know how to move the data from the sheet to the userform, I just don't know how to match my data with the selected month.

View 3 Replies View Related

Moving Data From Multiple Columns To Single Column

Mar 28, 2012

in moving data from multiple columns to a single column. I have attached a sample image from an excel file which details the requirement. The first column contains a qualifier, that should remain constant when the data from columns B to the end move to a single column "B". The number of columns for each row is different, however, there is a maximum number, say 25. As mentioned in the image, when the data from columns B to the end is moved in to column B, column A is retained fixed, to the original value, and the original data below it is pushed below. Any pointers to how this can be achieved by VBA or without VBA?

View 6 Replies View Related

Moving Data From Multiple Rows To A Single One

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

Moving Data And Deleting Rows, Part 2

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

Moving Data Cells From Multiple Columns To Single Column

Nov 14, 2011

I'm trying to work out how to take all cells with data from multiple columns and stack them in a single column.

Here's the history...

I have multiple part numbers in single cells in column A. I perform a text-to-columns function. The resulting part numbers spread across multiple columns (say, B through K). Now I need to get all the part numbers, in their own cells, stacked in column A for one continuous list of single cell part numbers.

Is there a VBA option for cutting only the data cells from Column B-K and pasting the data at the bottom of column A while avioding blank cells?

View 4 Replies View Related

Excel 2003 :: VBA - Only Copying Rows With Data In And Moving To New Worksheet

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

Addresses In Different Format To Columns

Apr 10, 2014

I have tons of addresses that I need to separate into different columns. It should be easily done by using Text to Column but the format is different so it didn't work.

1198 W 1520 N
CLINTON, UT 84015-5301
1198 W 1520 N
CLINTON
UT
84015-5301

798 HOMESTEAD AVE
HOLYOKE, MA 01040
798 HOMESTEAD AVE
HOLYOKE
MA
01040

16765 KENUIL CT
BRIGHTON, CO 80603-8481
16765 KENUIL CT
BRIGHTON
CO
80603-8481

The addresses are copied from a website so it is formatted as 2 lines. Tried to copy and paste (values only) to another sheet and the format was like this:

1198 W 1520 NCLINTON, UT 84015-5301

No space between "N" and "CLINTON".

View 2 Replies View Related

Moving Columns Of Data Into Another File And Saving File With New Filename?

Aug 30, 2013

I was wondering if there is a way to write a macro that will take an excel file that I have called Alldata and then have it copy the first two sheets to a new workbook named something else( FullSparameter_0) and then copy the name of the third and fourth sheet into the new FullSparameter_0 workbook and then copy column A-E from sheet dB Alldata.xls and paste it into the FullSparameter_0.xls workbook in the same dB sheet in the same spot. And do the same thing with the Phase Sheet. i know this sounds confusing, but im really new to the VBA stuff.

I just need 15 different files from the original Alldata.xls that will be named FullSparameter_0, FullSparameter_1, .., FullSparameter_14. but each file will have the same two first sheets, Setup Information and Cal Verification, and the same last two sheet names, dB and Phase, but then in the dB and phase sheets, it will copy over the next four columns of data. so in FullSparameter_0 it will have column A-E from sheet dB in Alldata.xls in the dB sheet in FullSparameter_0 (Same with the Phase Sheets). Then in FullSparameter_1 it will have column A and Columns F,G,H and I (for dB and Phase). In fullSparameter_2 it will have column A and then J,K,L and M. Each FullSparameter_X will have the exact same first two sheets and then the other columns for the last two sheets will be pasted into columns A-E in dB and Phase.

View 1 Replies View Related

Convert One Column Of Addresses Into Three Columns When Text To Be Grouped Is Not Even

Jan 10, 2014

I have checked to see if this has been answered before and can not find an exact reply. My problem is that I have a combined column of address: Suburb, State, Postcode - which needs to be broken into three which not be done accurately using tet to column or fx - left mid or right. The issue is that the Suburb could be made up of one or two Words with a space between. So I can not separate using text to columns as in some cases the suburbs comprised of two words will put the second word in the "state" column. It can also not be done using Left, Right,Mid, as they number of characters differs in each line. example problem with columns to text.jpg

COMBINED ADDRESS SUBURBSTATE??POSTCODE??POSTCODE??
ALTONA NORTH VIC 3025ALTONANORTHVIC 3025
BONDI NSW 2026 BONDINSW 2026
WOOLLOONGABBA QLD 4102WOOLLOONGABBAQLD4102
TOOWONG QLD 4066 TOOWONGQLD 4066
NOVAR GARDENS SA 5040NOVARGARDENSSA 5040

View 12 Replies View Related

How To Break Column Of Addresses Into Rows

Jan 3, 2011

I'm trying to work with a coworker get a list of addresses in a specific format. She received the file in notepad and I have saved the file in excel and the data looks like this. The addresses are all in column A and are 5 or 6 rows long and seperate by 1 or 2 blank rows of data.

Ms. Lisa ReynoldsManagerSmallville Operating Center123 Mockingbird LnLexington, KY 11111Mr. John DoeSVPMain Branch OperationsOperating Center Central Office121 Smith RdSamson, CT 22222Ms. Jane SmithVP - OperationsOperations Center 2200 Penn StNY, NY 33333Ms. Mary SmithVPOperations Center 4212 Elm StOperations, 10th FloorBuffalo, NY 14203Mr. Sam Jones

We are trying to get the data to look like this:

Ms. Lisa ReynoldsManagerSmallville Operating Center123 Mockingbird LnLexingtonKY11111Mr. John DoeSVPMain Branch OperationsOperating Center Central Office121 Smith RdSamsonCT22222Ms. Jane SmithVP - OperationsOperations Center 2200 Penn StNYNY33333Ms. Mary SmithVPOperations Center 4212 Elm StOperations, 10th FloorBuffaloNY14203Mr. Sam JonesSecondary Branch Operations121 Broad St.New YorkNY12110

There are roughly 5000 rows of data currently so if we have to copy and transpose each 5 or 6 lines of data into a row it will take a long time. Also I figure if I can at least get all the data into rows I can text to columns to split out the city, state, and zip.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved