Macro For Transposing Data In Cells

Dec 24, 2008

I need a macro that can do this in 1run.

Turn a sheet from, Row 1 would repeat as column A.
Acupressure Allergies Anxiety - Depression accupressure alergies anxiety therapy accupressure therapy allerg relief anxiety treatment acupressure allergen depressed acupressure chiropractic allergic depression treatment acupressure doctors allergies treating anxiety acupressure massage allergies asthma treatment for depression acupressure practitioner allergies cats Bipolar acupressure therapy allergies dogs Bi polar acupressure treatment allergies hives Dysthymic disorder chinese acupressure allergies medicine

View 9 Replies


ADVERTISEMENT

Transposing Data For Duplicate Cells?

May 8, 2014

I have a sheet of over 20,000 rows, each with 2 columns. Column A is numerical, Column B is name and address. There are duplicate entries in Column B, but Column A has unique entries.

I need to have 1 row per unique name and address with the numerical data from Column A relevant to that name and address either in one cell comma separated, or in cell C,D,E,F etc...

View 1 Replies View Related

Reorganizing And Transposing Data Through Excel Macro

Oct 13, 2012

Reorganizing data, that would probably be best achieved with a macro. I've attached a file that represents my situation fairly well. There are 2 tabs, an Input and Output tab.

The input tab has Names along the left side, that may change in number frequently. Then along the top I have metrics, and values corresponding to the names. What I need a macro to do is to basically create an "entry" in the Output tab for each value.

The output tab is in a specific order, but basically there are 8 columns. The 4 unspecified columns don't need to be filled. The other 4 represent data from the input tab. The "Metric Value" can simply be filled with 0s for now.

Excel - VBA Assistance.xlsx

View 5 Replies View Related

Transposing Every 10 Cells In A Column To Row

Dec 7, 2013

I am working with a huge database and need to transpose every ten cells in a column to ten rows.

Below is what I would like it to do, currently columns 90-0 are blank and I am trying to get a script to do it as I have over 550 subjects.

Subject
EconSlideQ6.RESP
90
80
70
60
50
40
30
20
10
0

[Code] .......

View 9 Replies View Related

Transposing Noncontiguous Cells

Feb 22, 2008

Is there a function or VBA out there that would allow me to transpose non-contiguous cells? For example, say I want to transpose ther contents from cells A1, C1, E1, G1, and I1 on worksheet 1 to A1:A5 on worksheet 2.

View 9 Replies View Related

Transposing Cells Again... From Fixed

Sep 2, 2006

Attached is a sample of my challenges.

View 8 Replies View Related

Looped Transposing Macro

Mar 2, 2007

I work on some High-throughput Projects analyzing biological samples. The software we use for analysis spits out the data into excel. This is all well and good, being mostly automated. However, my issue arises when I get the output data. It's simply one long list of values in a single column. This could potentially be 384 entries. Depending on the parameters of our experiment, I'd like to be able to take a set number of consecutive entries and transpose them to another worksheet. However I don't know how to loop the function to get the specific entries transposed to their specific worksheet. I'm pasting a recorded macro I made to illustrate my point.

Sub HTSoutput1()
'
' HTSoutput1 Macro
' Macro recorded 3/2/2007 by Jack Rosa
'
' Keyboard Shortcut: Ctrl+q
'
Range("B2:B9").Select
Selection.Copy
Sheets("K562").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "2330"

This would have to be repeated for a column that contains 312 entries so that I end up with each destination worksheet containing a 13row x 8col table (or should it be called an array?).

View 9 Replies View Related

Transposing The Data

Aug 7, 2009

I have a user form which I would like info entered into various text boxes to be assembled in a way which can be copy and pasted. See the attached example to see the results I am looking for.

View 2 Replies View Related

Transposing A Data Set

Nov 18, 2009

I have been trying to transpose a data set. Transpose formula is not working with this data set. How should i proceed ?.

View 4 Replies View Related

Transposing Data...again In Another Dataset

Dec 19, 2009

I was recently helped very much by the forum moderator and code from D_Rennie in a file to transpose about 25,000 rows into a few organized columns. The code worked very well. I have a slightly new twist on the problem. I have uploaded a different file with a different data structure in the columns but the same need for the data to be transposed to the columns under the same headings. This VBA worked very well before, but I can't seem to re-use it for this new dataset.

View 8 Replies View Related

Sorting Data And Then Transposing It?

Dec 20, 2011

I have a set of chemicals from groundwater tests at various wells. I need to sort the data, but chemical names can prove to be frustrating, since often times numbers or letters are found before the chemical name. Essentially, I'm trying to sort like this example:

Further complicating things is that at the end there's a (m,p-Xylene) and (o-Xylene) thrown into the mix.

Is there an easy way to do this sort? I imagine I'll have to have some sort of reference list that the sorting looks to?

After I sort it and do some data manipulation, I have a macro that transposes the data like the example below:

Here's the macro:

Sub Step4_ReorgData()
Dim w1 As Worksheet, wR As Worksheet
Dim c As Range, LR As Long, NR As Long, NC As Long
Application.ScreenUpdating = False

[Code].....

View 9 Replies View Related

Looking Up Data And Transposing It In Array?

Jul 14, 2014

I have two columns of data of which I need to transpose into rows. Sounds easy enough but the data looks like the following:

Business Role
Person
Accounts Payable Voucher Reviewer
SMITH,JOHN

[Code].....

I have read some sites talking about index and column or using VBA.

View 6 Replies View Related

Transposing Data For Graph

Jan 17, 2010

I am trying to create a graph for each store in my data sheet.

I have a data sheet with a list of stores that lists the quantity that was ordered by each store for the month of December. Not all stores placed ordered on the same dates.

What I would like to do is take the data from store line and create three columns that are: ....

View 11 Replies View Related

Transposing Data For Import

Oct 25, 2006

I need to transpose some data for import into our accounts package, the budgets are produced in a fairly standard horizontal format with periods across the top & account codes vertically.

The import requires period data to be be transposed vertically

I have attached a worksheet with an example, is this possible in excel. the only way I have managed to accomplish this is via MS Access

View 8 Replies View Related

Transposing Macro: Take A Set Number Of Consecutive Entries And Transpose Them To Another Worksheet

Mar 3, 2007

I work on some High-throughput Projects analyzing biological samples. The software we use for analysis spits out the data into excel. This is all well and good, being mostly automated. However, my issue arises when I get the output data. It's simply one long list of values in a single column. This could potentially be 384 entries. Depending on the parameters of our experiment, I'd like to be able to take a set number of consecutive entries and transpose them to another worksheet. However I don't know how to loop the function to get the specific entries transposed to their specific worksheet. I'm pasting a recorded macro I made to illustrate my point.


Sub HTSoutput1()
Range("B2:B9").Select
Selection.Copy
Sheets("K562").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "2330"
Sheets("Nuclei").Select
Range("B10:B17").Select.............

View 9 Replies View Related

Transposing Relevant Data Between Sheets

Jan 27, 2014

I've taken part of our instrument service data and eventually want to trend the performance but have decided it will only useful to use data where I have 4 or more data points. However with so much data I want to automate the process rather than scrolling through the column. I assume this requires a lookup table of sorts but I don't know how to combine that and getting the other bits of relevant information (I.e. all dates and results).

GavInstrument Spec data.xlsx

View 1 Replies View Related

Transposing Data From Rows To Columns

Jul 8, 2013

I have a piece of equipment that I use for temperature profiling temperature controlled chambers. The equipment has up to 16 channels for logging data, although we typically only use 9. The data output from the equipment is by Text file and we convert this to Excel for manipulation into a format that can be issued as a certificate.

The test file outputs the data mainly in a single column and similarly when it is converted to excel the data remains in a single column. To enable us to present the data in a format that is presentable as a certificate we most transpose the data from each channel from a single column into an individual columns for each channel. Because I have limited knowledge of Excel, this operation is very time consuming and I'm sure there must be a quicker way to perform this operation in Excel.

I have attached a copy of the text file and the resulting Excel file with the data in it's original format and also in the final format. This is only a snapshot of the data over 30 minutes and we normally carry out measurements over 24 hours.

View 7 Replies View Related

Extracting / Transposing Sheet Data?

Mar 9, 2014

I have the attached file with two worksheets D30(1) and Sheet 2.

I need to extract some of the information contained on the D30(1) sheet into the format as contained on Sheet 2. It is almost like a transpose but because all of the information is not needed and all cells don't have info in it, I didn't even try to transpose the D30(1) info.

Is there an easy way to extract the information from sheet D30(1) to present it in the format of Sheet 2? I need to do this for approximately 50 sheets similar to D30(1). Some sheets might even have more columns than the example provided.

View 4 Replies View Related

Transposing 25,000 Rows Of Data Into Columns

Dec 17, 2009

I really could use some help here. I have ~25,000 rows of data, contact information, and I am trying to convert it to columns with the corresponding 9 column headers (Full Name, Job Title, Company, Business Street, Busines City, State, Business Phone, Business Fax, Web Page, E-Mail Address). The challenge is that each record does NOT have the same number of rows...?? I am new to this forum, so please forgive me if this a stupid question. I've attached the data for three records below and also in the attached spreadsheet ...

View 14 Replies View Related

Transposing Range Of Data And Looping It?

Aug 28, 2013

Question: I have a VBA code that transpose every 7th row of a dataset in column A only, however I would like for the code to extend the transpose to (7 row and 3 cloumn).

This is the code:

Sub Transpose()
Dim A() As Variant
Dim i As Long

[Code].....

View 1 Replies View Related

Transposing Data With Paste Link

Mar 2, 2008

I have a worksheet that contains data in sheet1 Cells B2:B14. I need to copy this data into the Summary worksheet B2:N2 and to link it so that it updates on the summary Sheet.

When using Paste Special Transpose, it does not allow one to Paste Link. Is there another way to do this besides using a linking formula?

View 9 Replies View Related

Transposing Data & Tokenizing Strings

Aug 18, 2009

I have some data in the following format:

Spreadsheet #1
Code Country Digit
0582 ALGERIA 21312 Earth
0582 ALGERIA 21313 Earth
0582 ALGERIA 21315 Earth
0582 ALGERIA 21316 Earth

I have found the following example in the forums that has helped me get to the following:

Code Country Digit
0582 ALGERIA 21312 Earth, 21313 Earth, 21315 Earth, 21316 Earth

Sub a()
'Written by Barrie Davidson
Dim DataRange As Range
Dim DataSheet As String, SummarySheet As String
Dim Digits As String

Application.ScreenUpdating = False
Set DataRange = Range("A1:C" & Range("A65536").End(xlUp).Row)
DataSheet = ActiveSheet.Name
Sheets.Add before:=Sheets(DataSheet)
SummarySheet = ActiveSheet.Name

But I need it to be in the format:

0582 ALGERIA 21312 , 21313 , 21315 , 21316, Earth

I have tried working it out myself but am a macro/excel novice (and have gone cross-eyed).

There are over 10000 lines of data so unfortunately I cant just do it manually.

View 9 Replies View Related

Transferring(Transposing) Data To A Finished Sheet

May 25, 2006

I am trying to find an automatic way to create a finished report format that draws information from a different sheet within the same workbook. The trouble I'm running into is:

The raw data consists of some lines without any data, and some lines with data. The purpose of the final report is to display only those rows with data in them.

I have a sample spreadsheet attached, with the first sheet containing raw data, and the second containing a rough Idea of what I'd like the final infomation to look like. Somehow this seems like it should be really simple, maybe doable with an if statement, but I can't figure out how to get it to just ignore the empty rows without skipping a row in the final doc.

View 2 Replies View Related

Transposing Columns Of Data Into Rows Of Data

Apr 28, 2009

The title makes it sound simpler than it is. I have 2 columns of data which I need to transpose.

The difficult bit is that Reference Number 1 may have 3 reasons (so therefore 3 rows) that need transposing into 4 columns (reference number, reason1, reason2, reason3)

Is there a way to do this?

I have attached a spreadsheet with the original table and how I need the end result to look.

View 14 Replies View Related

Excel 2007 :: Transposing Large Amounts Of Data

Nov 30, 2010

I have data that as 872 columns - each representing a question ID (headers in the first row). I then have 1494 rows of data where each represents 1 unique person. In other words, A2 = Person ID and B2-AGN2=the potential answers to the questions.

What I'd like to do is compact this into 3 columns: "Person ID", "Question", "Answer".

"Person ID" will have duplicate values for each question that is answered.
"Question" is the Question Text
"Answer" is each answer to each of the questions.

So in a condensed form, my data looks like this:

ID Q1 Q2 Q3 QAge
3 1 5 8 35
4 1 2 12 64

And I would like it to look like this:

ID Question Answer
3 Q1 1
3 Q2 5
3 Q3 8
3 QAge 64
4 Q1 1

etc.

It would be fantastic if Excel has the functionality to ignore null answers and therefore just not even bother populating Question ID when an Answer is blank (e.g. they didn't report an age, so QAge doesn't show up under the new "Question" field), but I have no idea if that's doable.

I have a lot of datasets like this with a varied number of rows and columns, so any way to adjust whatever formula/macro is out there to work for those. I'm terribly new with macros and so I've been having difficulty adapting them if I need to.

I'm using Excel 2007.

View 10 Replies View Related

Transposing Column Series Of Data To 2nd Sheet In Rows

Jun 19, 2008

I have a column with (for example) 5 entries A1, A2, A3, and so on. I would like to pick up this data, tranpose it into a row, put it on the 2nd sheet, but (again for example) be specific about where i want it to go. I may want to have A1 at B2, A2 at E2, A3 at H2, etc

-A B C D E F G H
1x
2y
3z
4
5

becomes

-A B C D E F G H
1
2 -x - - y - - z
3
4
5

View 9 Replies View Related

Excel 2010 :: Transposing Data With Blank Rows In Columns

Feb 10, 2014

We utilize large data sheets that can be as large as 300K in rows and 10 to 15 columns wide. Because of how we receive the data, we are forced to manipulate things so that all matching data for a record ends up on a single row. (e.g. Record#, Document Type, Husband Name, Wife Name, Wife Maiden Name, Etc.)

Right now here's how the data is received:

a
a
a
b
b
c
c
c
c
d
d
d
e
e
e
e

Using two vba scripts, we first separate the data with row spaces between the unique data as follows:

a
a
a

b
b

c
c
c
c

Then with another script, we transpose the data as follows:

a a a
b b
c c c c
d d d
e e e e

When we transpose the data, the end result starts at the top of the page and go down eliminating the original blank rows. Not a huge issue but I would like to be able to maintain the original data format of the rows so that the data matches the original sheet line for line. The end result would give me the data as follows...

a a a
b b
c c c c
d d d
e e e e e

where the vertical gaps between the letters matches the original rows. Like I said, not a huge issue since we can rejoin the transposed data to the original data fairly easily. But it would be nice if we could end up with the above format for speed sake.

The two scripts we use, one-to insert the rows and two-to transpose, take a very long time to run with the transpose script taking the longest by far. On a 30K row sheet, it will take on our systems around 30 minutes to transpose and about 15 minutes to insert rows. Because we have several columns that need to be transposed, a 30K row sheet will take at least 2 hours to complete. A 300K row sheet, that will take 10 to 15 hours to complete.

Is there any way to speed up the scripts either by upgrading to a faster CPU and or writing the scripts to preform faster?

My preferred solution would be to write (have) a formula to preform the transposition that gives me the results as noted above since formula's run so much faster than vba. Is this possible? I have tried all kinds of formulas and can not come close and of course the straight transpose function does not give me the solution I need as noted above.

I have enclosed an excel 2010 spreadsheet with 10K rows of data in rows along with the scripts I use (nothing sensitive here). The tabs at the bottom shows you the data before I transpose, then the data after it has been transposed . To speed up the scripts, I have stripped away the all the rest of the data from the original sheet except just what I need to transpose at one time. Once that is completed, we then re-join the transposed data with the original sheet. The six digit number you see to the far left of the data is the record ID number from the original data. We use this to rejoin the transposed data with the original data so that we know everything is back where it should be. (Note: The insert rows script is run on the original data and not the data you see on the enclosed spreadsheet. That is the only way we can generate unique rows with matching ID numbers. We arrive at this by taking the original data, concatenate the record ID with the column we want to transpose and add a # between the two so that we can break things back apart after the transposition using the text to column function using the # as the separator.)

The sheet I have attached is in the 2010 Macro Enabled format...(xlsm format). We use the xlsb (binary) format for the data to reduce the file size as our normal procedure and run the macros from inside that format. Changing from the xlsx to xlsb format did seem to speed up the scripts a bit and greatly improved the file performance as a whole e.g. saving and loading.

One thing I have done to speed up the scripts is to strip all the data away that is not needed for the transposition. That did work but only a marginal amount.

We are using windows 8.0 with 4G memory and your basic processor speed...e.g. nothing fancy.....just your basic stock computer. Nothing else unusual is installed or running on the computer or at the time the scripts are running.

For those of you that process large sheets, how much of a performance upgrade will we see in processing our scripts by either upgrading memory to 8G (or more or much more) and or getting a faster processor? Or have we reached the maximum script speed already? Or is this a limit to Excel.

One other issue to note: As I stated above, on the 30K row sheets, not a super problem with about 2 hours needed to run the scripts on all the data on the sheet. But on the 300K row sheets, it can take 12 or more hours to run and there are times when things 'lock up' running the scripts on sheets this size.

View 5 Replies View Related

Macro To Return Data Of Cells Based On Other Cells

Apr 23, 2014

I have a worksheet that I want to populate with specific data from another worksheet.

If all of the following are met:

1) Column A equals "763" and

2) Column K is formatted Red (255, 0, 0) from conditional formatting

3) Column P is blank

Then in my blank worksheet I need to display the data from Columns A, B, D, K, O

View 4 Replies View Related

Transposing An Array

Nov 18, 2009

I want to transpose the data set. I am using the following formula

HTML
INDEX(Dataset!$B$2:$Y$13366, MATCH(B$1, Dataset!$A$2:$A$13366, 0), MATCH($A2, Dataset!$B$1:$Y$1, 0))

But there seems some problem with the reference of year (I want the range will change From A2:A15 then it will start from A17:A31 like this until my row ends at 13366).

View 4 Replies View Related

Transposing Links

Nov 10, 2005

I have very large sections of data which I need to transpose as links.
Problem is that the copy/paste links function cannot be followed up with the
copy (the linked cells)/transpose option, without having the transposition
screw the links up.

View 9 Replies View Related







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