Converting Data In Two Columns Into A Row On Separate Sheet
Oct 20, 2011
I have data in two columns on the same sheet that I need to transpose into rows on a separate sheet (same workbook). One problem is that I need to reference off one of these columns (column B - in Sheet "Gp Trg Plan") as the number of lines will vary.
Example - Sheet "Gp Trg Plan"
Column B Column D
Tower Module 1
Tower Module 2
Tower Module 3
Building Module 1
Building Module 8
Street Module 6
Ideally the row will look like.
Example - Sheet "Status WS"
Column A Column B Column C Column D Column E Column F Column G
Gp Name Sub Gp Person 1st Mod Date 2nd Mod Date
There is an undefined amount of training modules (columns D in Sheet "Gp Trg Plan"), but no more than 10.
View 4 Replies
ADVERTISEMENT
Mar 6, 2014
I have a string of data coming from a SQL Server data connection into my workbook. The value in the column is a text string that is pipe delimited. I need a macro to parse the data from that column into applicable separate columns. I would typically use text to columns for this and parse it out manually... but the tool I'm creating is one of the automated variety, so that will not suffice for this application. I need it to do this automatically when the data connection refreshes.
U:U AQ AR AS AT AU AV
to
Produce|Fruit|apple|banana|cherry|date Produce Fruit apple banana cherry date
View 2 Replies
View Related
Nov 18, 2013
I have a workbook with four tabs or four sheets.
Tabs 1, 2 and 3 have a column of data (Column A) on each sheet. They all start from the same cell. Each cell of data are just numbers. The column (an array) of data will not have blanks between.
But, they are not the same number of data. They vary.
Meaning, Tab 1 may have 15 numbers (A1 to A15), Tab 2 may have 20 numbers (A1 to A20) and Tab 3 could have 5 numbers (A1 to A5). Each iteration of the workbook may have different number of data in the A column on these tabs.
Now on Tab 4, I want combine the data from all three tabs into one column (in column A).
So, Tab 4 has a column A with data from Tab 1 copy and pasted to (A1 to A15) as values, then (A16 to A35) have Tab 2 data copy pasted as values and (A36 to A40) have Tab 3 data copy pasted as values.
Basically, the macro on Tab 4 has to count the number of rows on each Tab that are populated with data values and figure out to copy all the data on Column A from each three tabs and paste the data value into the Tab 4 in one column of data (in values).
View 6 Replies
View Related
Jan 26, 2009
I have rxd a pdf file that I need to manipulate I have copied the data into excel and now need it in individual cells:? The data looks like this: 02392950067 19/12/2008 14:33:35 02392950053 Portsmouth 1.9 £0.03. this is:
phone number calling | date called | time called | number called | duration in minutes | cost of call. If you're out there I'd be more than happy to hear from you. the cells to cut the data into will be formatted to take the data in the correct form. 02392950067 19/12/2008 14:33:35 02392950053 Portsmouth 1.9 £0.03
phone number calling | date called | time called | number called | duration in minutes | cost of call.
View 3 Replies
View Related
Feb 20, 2009
I have four columns of data, as follows:
label 1, value 1, label 2, value 2
I need to create a formula in the fith column that for each line will tell excel to:
look for entry in 'label 1' in 'label 2' if there is a match, then subtract value 1 from value 2, display result.
I have tried doing this with SUMIF but am getting nowhere fast....
View 7 Replies
View Related
Jan 12, 2010
I am trying to figure out an efficient way to convert data into Excel. The data is not in a row/column format. The format that I received the data in is as follows (2 columns; column labels repeating for each name in column A, and the corresponding data in column B. See below. Any ideas on how to best to convert this data into column labels across the top and data in rows under each column heading?
Column A Column B
Name John Doe
Address 123 Maple Street
City Any City
State Any State
Zip 12345
First Name John
Last Name Doe
BLANK ROW
Name Jane Doe
Address 123 Maple Street
City Any City
State Any State
Zip 12345
First Name Jane
Last Name Doe
View 9 Replies
View Related
Jun 20, 2011
My problem: I have downloaded national economic data from IMF with several countries for several years. I need several variables and must extract them individually so I a need a clever solution to my problem.
I currently have data that is sorted like below. The "-" indicates a numerical gdp value for the given country for the given year.;
(FYI I could not get the "-" to align beneath 2001-2004 and year respectively.)
Country 2001 2002 2003 2004
x - - - -
y - - - -
z - - - -
The identifier here is country, and the data is as such structured as cross sectional data, with each yearly account of gdp being a variable. Now, I need this data as panel data, organized as below:
Country year gdp
x 2001 -
x 2002 -
x 2003 -
x 2004 -
I sort of say, need to "flip it". As I have 180 countries and 11 years, and need to do this with 2-3 variables, I need either a formula or a VBA to run.
View 12 Replies
View Related
Aug 23, 2009
How can I convert a table from rows to columns as shown below??? Thanks for the feedback...
Current file with:
Unit_no work_order_date
1234 1/1/2005
1234 6/10/2006
1234 10/1/2007
4321 2/3/2004
4321 5/12/2005
Change to:
Unit_no work_order_date work_order_date2
1234 1/1/2005 6/10/2006
1234 6/10/2006 10/1/2007
4321 2/3/2004 5/12/2005
Will be working with data to sum data between the two work order dates...
View 9 Replies
View Related
May 22, 2013
how to separate .csv data into different columns in excel?
e.g this .csv data
2012.09.07,07:00,125.78800,126.12500,125.76800,126.02300,6664
2012.09.07,08:00,126.02100,126.19800,125.93900,126.05000,4707
2012.09.07,09:00,126.05100,126.11300,125.96700,125.99800,4178
2012.09.07,10:00,126.00000,126.02900,125.71700,125.77800,6389
2012.09.07,11:00,125.78300,125.91400,125.62800,125.66400,5388
2012.09.07,12:00,125.66300,125.86900,125.04100,125.12300,10447
[Code]....
View 11 Replies
View Related
Jul 22, 2014
I have some data which i want to split, i have tried " Text to columns "on excel but couldnt find a way it works.In one cell my data is exactly like this:
Name of product
Code of product
Loc.: here is the location
expire date:
I would like each one of these itens on one column, like
A b c d
Name of product code of product Loc.:here is the location expire date:
View 11 Replies
View Related
Jan 28, 2014
I have a huge data and want to know the best VBA or formulas to separate to consecutive columns.
Example :
BERNATTE QUIGLEY 10 WESTBOURNE ROAD STOCKTON HTH WA4 6SE
J QUINN 100 CHRISTOPHER TAYLOR COURT FLAT 18 B30 1ER
JOAN QUINN LITTLE COURT CT6 6PT
PAMELA QUINN ROAIN BANKS COTTAGES CT4 5PU
[Code] ..........
View 3 Replies
View Related
Mar 28, 2014
I am working on a workbook, it's not my workbook so I can't change how it's laid out.
I am trying to figure out a way that excel will check 2 different columns for a code that is manually entered, then enter the codes description in another cell.
It's basically a deposit and expense ledger.
One column has expense codes and names, the other deposit codes and names.
I want to be able to enter the code letter into a cell in the ledger and excel will enter the code description into another cell.
View 11 Replies
View Related
Jan 3, 2014
I'd like to copy information from a PDF into a worksheet. The information looks like this:
1 PAOZZ 5310-00-918-0482 80205 NAS1291-7 NUT.............................................................................. 1
2 PAOZZ 5305-00-995-2125 80205 NAS603-7P SCREW.......................................................................... 2
3 PAOZZ 5305-00-866-0937 80205 NAS603-8P SCREW.......................................................................... 14
4 PAOZZ 5306-01-106-8238 80205 NAS6603-3 BOLT ............................................................................ 8
When I paste it into excel it all gets entered into one column, I'd like to spread it out over 7 columns. Is there an efficient way to do that?
View 2 Replies
View Related
Dec 10, 2009
I have text in column A and text in column B. I would like to create column C and place the number 1 in each row whenever columns A and B contain the text I am looking for. I could then use C to filter all the 1's with ease (by way of macro or the autofilter). In my problem, column A contains the following text per cell:
B
B
B-A
B-A
B-V
B-V
Column B contains:
THR
THR-MATT
HF-MATT
HS-HS-THR
HS-MATT
I need to filter based on column A having the letter "A", or column B having the word "MATT". Columns A and B do not have to contain both "A" and "MATT", respectively, in order for column C to have a 1.
View 4 Replies
View Related
Jan 12, 2007
how to separate data such as this, into different columns?
85878; null;OMX;OM;2004-09-13 08:58:29.0;691.91;OMX Stock Index;693.01;688.67;691.91;15055;0.0;14.64;0.0;0.0;2004-09-10 00:00:00.0;0.0
Today this data is inserted into one cell, in one column, but in 50 000 rows (in 9 different sheets!). I would like to separe it into separe columns so I could run calculations on the data. Separating it manually would take me at least 1 year,
View 4 Replies
View Related
Aug 9, 2009
I have a 45 page spreadsheet with over 3500 contacts and the data is currently listed in individual cells as seen below:
John Smith
Director of Business Travel Sales
ABC Hotel
1200 Market St.
Philadelphia, PA 19107
Phone (215) 555-1234
Fax (215) 555-4321
jsmith@abchotels.com
www.abchotels.com
I want to convert the each item [data] above into separate columns so I can then save it as a CSV file and then export th data into an email list; but I have no idea how do to this.
View 9 Replies
View Related
Apr 10, 2013
I have a glossary with 400+ rows in column A. See examples below. I'd like to separate them so that the term is in column A and the definition is in column B. Once Done I will not need the . Normally instead of that tag I would have a hyphen separate the term from definition but the fact that some terms have a hyphen like "D-VHS" was throwing me further. In the end I will not need a separator because everything will be in separate cells.
DSL Digital Subscriber Line is a technology for bringing high-bandwidth information to homes and small businesses over ordinary copper telephone lines. A DSL line can carry both data and voice signals and the data part of the line is continuously connected.
D-VHS Connects a digital audio/video cable for use with some D-VHS digital VCRs.
E-Mail Messages sent to a customer's receiver that are viewed on their television screen. Pending messages are indicated by an icon on the on-screen status display (channel marker) and by having the Power button flash. (Not all receivers have a blinking light.)
View 4 Replies
View Related
Nov 20, 2012
I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.
The device label extraction is similar to this:
DCM1005-1
DCM1005-10
DCM1005A
MTR1005-1
MTR1005-10
MTR1005A
I want to create 3 columns from the device label: (I separated the column with commas)
A1, B1, C1, D1
DCM1005-1, DCM, 1005, 1
DCM1005-10, DCM, 1005, 10
DCM1005A, DCM, 1005, A
MTR1005-1, MTR, 1005, 1
MTR1005-10, MTR, 1005, 10
MTR1005A, MTR, 1005, A
View 5 Replies
View Related
Oct 9, 2013
How can I separate the following numeric/text combination into two (2) separate columns in Excel?
302ALTO
406AMZN
451AMRC
404AMAD
605ANCC
405ADRC
The result would be:
302 ALTO
406 AMZN
451 AMRC
404 AMAD
605 ANCC
405 ADRC
View 6 Replies
View Related
May 22, 2014
I need to split the data into worksheets (see attached) by the Advertiser column and then by the deal year and deal code columns. I need each worksheet to be named per advertiser and deal year_deal code. I took off and replaced the data since it is sensitive information.
creating a macro or implicating one into the workbook to run for future reporting.
View 13 Replies
View Related
May 21, 2008
i have a long column pair of data, each entry in its own cell:
10/5/20088:30:00 AM
10/5/20088:46:00 AM
10/5/20089:14:00 AM
10/5/200810:18:00 AM
10/5/200810:42:00 AM
11/5/20088:30:00 AM
11/5/20088:46:00 AM
11/5/20089:14:00 AM
11/5/200810:18:00 AM
11/5/200810:42:00 AM
12/5/20088:30:00 AM
12/5/20088:46:00 AM
12/5/20089:14:00 AM
12/5/200810:18:00 AM
12/5/200810:42:00 AM
13/5/20088:30:00 AM
13/5/20088:46:00 AM
13/5/20089:14:00 AM
13/5/200810:18:00 AM
13/5/200810:42:00 AM
14/5/20088:30:00 AM
14/5/20088:46:00 AM
14/5/20089:14:00 AM
14/5/200810:18:00 AM
14/5/200810:42:00 AM
how can i program a macro to 'split' this column according to date? please refer to the attached picture as an example. i know this is probably a simple question but please bear with me i'm still new to excel programming.
View 14 Replies
View Related
Jun 18, 2013
I have repetitive task of copying and pasting. Macro to sort some data.
I have time values (sorted in order) in Column A and a value in Column B.
Sheet1 Â AB1TimeValue
200:00:002300:00:002400:00:001500:00:002601:00:004701:00:002802:00:001902:00:0021002:00:0031102:00:0041202:00:0011302:00:002
I need separate the time each hour of data into columns. So the above would become
Sheet1 Â ABCDEF1TimeValueTimeValueTimeValue
200:00:00201:00:00402:00:001300:00:00201:00:00202:00:002400:00:001Â Â 02:00:003500:00:002Â Â 02:00:0046Â Â Â Â 02:00:0017Â Â Â Â 02:00:002
There are a different number of rows of data for each hour and sometimes there might not be any data for a specific hour.
View 4 Replies
View Related
Oct 31, 2009
I have a separate sheet(Coverage.jpg) that records some data which at this stage has to be entered manually.
The data comes from another sheet(officers.jpg), each row is 1 flight and the days are usually separated by a blank or grayed row.
I was wondering is there a formula that will collect the data automatically.
As you will see on Coverage.jpg it is broken down into Number of flights(per day), how many flights were covered by 2 or more officers and how many covered by 1 officer.. and then the graph generates off the data.
Is there a formula or something that will enter the per day data?
I have just added some false data to show you how some things get recorded.
View 14 Replies
View Related
Aug 29, 2006
I have a spreadsheet that has a resource table, project stage table and an approx 50 different project sheets. (The 50 sheets are duplicated layouts, just different project names)
Based on the data on the first two sheets I would like to populate the individual project sheet.
For example:
1.In the individual project sheet there are 5 stages in each quarter.
2.The project stage table sheet tells you want stage the individual project is in for the relevant quarters.
3.The resource table sheet tells you how many resources are required for that stage.
Based on this information, I would like to populate the individual project sheet with the information.
E.g. If the project is in the 1st stage, it would then go to the resource table and take number of resources allocated for that stage and populate the “relevant” field in the individual project sheet with the correct value.
I've attached the spreadsheet to hopefully better illustrate this.
View 6 Replies
View Related
Jan 31, 2014
I have a data set that I wish to look up the data from one column and if it is greater then 0 write it in another column separated by commas. Here is an example:
The data is dates that a service was provided and how many time that day it was done and not everyone gets the service on the same days. I would like to summarize the days of the month that service was provided not number of times into 1 cell.
A B C
Row 1 November
Row 2 1 5 15
Row 3 1 0 2
Row 4 0 1 3
November is in A3
If A2 is greater then 0 I want to write A1 A2 If A2 and A3 is greater then 0 I want to write A1 A2, A3 If A2 is 0 and B2 is greater then 0, I want to write A1 B2
View 14 Replies
View Related
Feb 5, 2014
I've got a problem with organizing my data. I've performed a study with several participants, each of which does several trials with 8 conditions (1,2,3,4,5,6,7, or 8), with each participant doing a condition more than once. I also have a separate column telling me whether they responded correctly or incorrectly (with a 1 or a 0).
I need to find a way to produce a new column to identify whether they got each condition (of the conditions 1,2,3,4,5,6,7, or 8) correct separately, i.e one column for responses to condition 1, one for condition 2 and so on....
It would also be useful if there was a way that once this is done I could summarize their accuracy of responses to each condition.
I've attached an example of my data. excel problem example.xlsx‎
View 14 Replies
View Related
Nov 18, 2013
100% Bran Nabisco 70 130 10 5 6
AlI-Bran Kellogg 70 260 9 7 5
All-Bran w/Extra Fiber Kellogg 50 140 14 8 0
I am having trouble parsing the numeric data from the following table into separate columns. The five different numbers (ex. 70, 130,10,5,6 in the first row) represent nutritional info about the product. As you can see, the number length varies for the different categories. How to get these 5 numbers into their own columns using LEFT, RIGHT, MID, FIND, etc. functions.
View 3 Replies
View Related
Jul 23, 2014
Trying to consolidate and Merge Data on a Separate Sheet
View 4 Replies
View Related
Oct 23, 2009
I use Excel 2003. I need help building a macro, please, that will copy data to a specific page in another workbook based on two criteria. Here's the deal:
The data in Workbook A, Sheet 1, Cell A1 may contain the word ALPHA, BAKER, or CHARLIE. Cell A2 may contain the number 1, 2, or 3. Cell B1 contains the data I want to collect from various Workbook As and keep in a list to analyze.
Over in Workbook B, Sheets 1 through 3 are named ALPHA, BAKER, and CHARLIE. Rows A, B, and C are titled 1, 2, and 3.
How can I copy the data from Workbook A, identified as ALPHA 3, to it's place in Workbook B, Sheet ALPHA, Row C?
Furthermore, Workbook A is a one time form will be used many times. Thus, when I copy B1 to Workbook B, Sheet ALPHA, Row C, I need to paste the data in the first empty cell in the row.
View 9 Replies
View Related
Dec 5, 2008
I am trying to count the # of rows in a spreadsheet in which there is non-blank text data in 2 separate columns. For instance, if the spreadsheet looked like the one below (dashes just for formatting purposes):
NAMES-------THIS-------------THAT
Mary-------some text--------some more text
John-------<blanks>---------just text here
Sue--------just some here-----<blanks>
Dave-------something--------something else
Adam------<blanks>-----------<blanks>
The total # of rows with something in both the "THIS" and "THAT" columns above would therefore be 2.
View 4 Replies
View Related
