Advanced Transpose Macro

Dec 19, 2008

I am desperately seeking a better way of transposing a large amount of data. By the looks of other posts, my objective is possible with a bit of VBA. Sadly, I am VBA illiterate.

My data looks like this (simplified) [ data is separated by columns "|".

A | May 1 | Jun 25 | Aug 9 | Dec 12
B | Apr 1 | Oct 25
C | Jan 6 | July 7 | Nov 11

I want to make it look like this:
A | May 1
A | June 25
A | Aug 9
A | Dec 12
B | Apr 1
B | Oct 25
C | Jan 6
C | Jul 7
C | Nov 11

I have accomplished this in the past, but it involved importing the table into access, then creating a query for each of the "date" columns, then copying and pasting the results into one table. It was a painful experience considering there are over 2,000 lines on the original table (resulting in 25,00 lines in the combined query).

View 12 Replies


ADVERTISEMENT

Advanced Sum Macro

Jan 27, 2010

hello honorable excel profesionals.
here is what i want the macro to do:

must start from the top of the colum and count cells with specific data in them:

if ''X'' or ''Xa'' or ''Xb'' -> start counting how many cells in a row have the value specified.
from here two things can happen:

if a cel with a value ''Y'' is met -> stop
if a cell with value '''Ya'' or '''Yb'' is met -> countinue counting untill Y is met

when I say stop I mean that the macro should imput, ona separete table:
1.total number of how many counts where made.
2.how many counts there where with a lenght of 1, how many with the lenght of 2 and so on

here is an example of data line and a table I want to have after macro is done.

Hope that this isn,t a problem to write otherwise il have to do this manualy - about 5000 rows of data...

View 10 Replies View Related

Advanced Macro

Aug 3, 2007

I have a difficult one I think? I have a workbook in Sheet 2 I have a page with data.

I would like to create a Sheet 3 that would look at Sheet 2 and copy all rows (Range A to V) with the asterisk displayed in Column B... IF MORE THAN 30DAYS AFTER THE DATE ENTERED IN "Column C"....

View 9 Replies View Related

Advanced Filter - Macro

Mar 24, 2007

Having big problems getting an advanced filter macro to work on-going (meaning again and again and again once I update the table it filters from).

Macro is as follows:
Range("A1:O1").Select
Range("A3:O65536").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"H1:H2"), CopyToRange:=Range("T1:AH65536"), Unique:=False
Range("A3:O65536").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"G1:G2"), CopyToRange:=Range("AM1:BA65536"), Unique:=False

Do I need to specify the sheet that this filter should occur on? If so could someone indicate how I would get that into the macro?

View 9 Replies View Related

Advanced Filter Macro Does Not Work?

Feb 15, 2013

I've written a macro in an attempt to automate the advanced filtering in Excel.

VB:
Sub FilterData()
Sheets("Filter").Select
Range("B10").Select[code]....

View 5 Replies View Related

Automate Advanced Filters With Macro

May 29, 2007

I need to use the Advanced Filter tool to allow the user to filter (in-place) the Guests worksheet while providing the following summary stats: total revenue, ave. revenue, max/min revenue and total number of tours ie the user should be able to enter any criteria below the database to to show only those records (and summary stats) that satisfy the criteria.

To automate the operation of the advanced filter tool, I need three macros. "SelectRoom" and "SelectGuest" macros should prompt the user to enter a value through an input box to filter the data according to a client's name or language tour. The third macro, called "ShowAllGuests" should clear the criteria row and dispay all clients in the database.

The first two macros should include an error message to prompt the user to rerun a macro if no clients satisfied the criteria while the "ShowAllGuests" macro should include a message box statement at the end to display a short message giving credit to the macro author. These macros also require a button each (three in total) in the Guests worksheet!

View 3 Replies View Related

Macro Code Using Advanced Filter

Jun 28, 2008

This is quite a easy one. I am a begineer so I don't know how to do it. I keep getting a runtime error. I want create a macro that does the following. I have this data(it is made up).

NameSchoolFee
JohnFDU700
MarkeyNYIT400
NickNJIT500
SamBU100

I want it to autofilter school njit

NickNJIT500...........

What would be the code for this? I want use the code for bigger data?. Also if you have a file which is 100 lines long and your macro does a series of steps for that file. Then you have another file and you want the same steps done but it is 500 lines long. How would you write a macro thatrun the steps taking in the last line and not just a 100 lines.

View 2 Replies View Related

Advanced Loop In Macro Find Maximum Value

Feb 5, 2009

I've an excelsheet with quite advanced formulas that area really nested and using quite a bit of iteration.

What I would like to do is finding the maximum of

A while Amin<A<Amax
By changing B

There is a Variable C than should be constant unless it becomes larger than Cmax ( Cmax dependas on A,B ,C, D and all kinds of things)

If C is > than Cmax there is a variable D that can be changed has to be less or equal to Dmax

So the question is how to find the maximum of A while fulfilling all these variables?

What I've come up with so far is:

View 9 Replies View Related

Advanced Filter Active Sheet Macro Code

May 9, 2008

the macro mentioned below has been assigned to a combo-box form. I have 2 different datasets in the same sheet, they have the same headers (but named differently, myhead and myhead1) and the same adv filter criteria range (named as "dcrit") is applicable to both datasets. So, if an item is selected from the combo-box, both the datasets should get filtered according to same criteria. how i can combine the 2 IF LOOPS mentioned in the code?

Sub myfilt()
Application. ScreenUpdating = False
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
If Not (Range("indsignal")) Or Not (Range("countsignal")) Then
Range(Range("myhead"), Range("myhead").Offset(1, 0).End(xlDown)).AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:=Range("dcrit"), Unique:=False
End If
If Not (Range("indsignal")) Or Not (Range("countsignal")) Then
Range(Range("myhead1"), Range("myhead1").Offset(1, 0).End(xlDown)).AdvancedFilter _
Action:=xlFilterInPlace, CriteriaRange:=Range("dcrit"), Unique:=False
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

View 3 Replies View Related

Macro To Automatically Add Totals To The Bottom Of A List Created With Advanced Filter

Mar 5, 2008

if it is possible to write/record a macro that will automatically add a summary/total row immediately below the data that is created from an advanced filter. (XL00)

View 12 Replies View Related

Macro To Transpose Data?

Jun 7, 2014

I need macro to transpose the raw data I have extracted from a Biometrics file into the format I need to compute for work hours.

Sheet 1(raw data)

Sheet 2 ( format i want the data to look like)

View 3 Replies View Related

Macro To Transpose Data

Feb 9, 2009

I have data that gets dumped from a program into a nasty horizontal format that I need to get transposed into a verticle format. As it stands now, the info is reported with hourly data spread accross rows. I need the hourly data in one column. See attached sheet for an example of what I need done with the data. I'm looking for a macro to take my "original" sheet and create my "new" sheet. Note: a macro that can do this would save me days of time.

View 4 Replies View Related

Use Of Transpose In Excel Macro

Jul 31, 2012

I am receiving the results of a Web-based test via e-mail. I am able to paste the e-mail's contents into an Excel spreadsheet and use it to manipulate the data as necessary, with one exception, namely an answer that requires the test taker to write a multi-paragraph e-mail response. I am able to identify which cells in Column A this answer occupies, for sake of argument, A60 to A70.

I want to have the contents of these cells appear in a single cell, B60, with linefeeds.

I'm able to do this with the following macro:

Code:
Sub paste()
Range("indirect(i5)").Value = vbCR & Join(Application.Transpose(Range("indirect(e1):indirect(e2)")), vbCR)
End Sub

where the indirect functions refer to cells where I've identified the beginning and ending rows of the range of cells I want to combine. I've also concatenated an additional vbCR to insert a leading carriage return, desirable for cosmetic reasons.

With an interactive button on the spreadsheet to activate the macro with a single click, it's pretty nifty.

Now, I have run into a problem when indirect(e1) and indirect(e2) have the same value. In that case, the macro fails. How do I recode or work around this?

View 1 Replies View Related

Transpose Macro In Excel

Oct 15, 2012

Full Document (2)
A

2
24 IP LAW GROUP FRANCE

3
48 rue Saint Honor

4
75001 PARIS

[code]....

I have many data in above format. I need to put it in columns. Also there is NO uniformity in rows. The data consists of Company name, Address, Tel no. Fax no., email and contact names and in some cases website also.

So when the data is transposed into columns, due to non uniformity the data gets jumbled. PColumn headings as Company name, Address, Tel no. Fax no., email, website and contact name.

View 4 Replies View Related

Macro To Transpose Data

Oct 14, 2013

I want to transpose my data. Please see excel file. The original data is of 5 persons (in column) and working hours are recorded over the 35 days (day 1 to day 35 in rows). There are 35 entries for each persons.

I want to do a longitudinal analysis. So I want to transpose data in such a way that each person is repeated 35 time one column, with day in next column (1-35), and working hours are give in front;

For detail see attached excel sheet.

Original table

ID
D1
D2
D3
D4
D5
D6
D7

[Code] ...........

Want to make below table

ID
Day
Hour

1
1
2

[Code] .......

View 2 Replies View Related

Transpose Data Via Macro

Jan 29, 2014

Have been stuck on an interesting issue I currently have data as set out in the table below.

name
ID
course 1
course 2
course 3
course 4

[Code]....

I need the course list to be restructured such as this below. I have tried using a vlookup with extra bits and a pivot table but cannot achieve what i am after, was thinking maybe a macro that could transpose each row as it drops down but not sure where to begin on that one.

steve
course 1
100
steve
course 2
94

[Code]...

View 3 Replies View Related

Need Macro To Transpose Data (MS OS X)

Nov 5, 2008

I have excel file that have the following columns: Sample ID, Analyte Name, Concentration, RSD. These columns are filled down.

I would like the analyte names should be the column headings. Then the rows with the sample ID, Concentration, and RSD - these rows are filled in with elemental concentrations, and rsds associated with those concentrations.

It is not quite a simple transpose, the rows should start over every time the sample name changes. I will be very grateful for any help with this and would gladly send a file to anyone who would help me. Sometimes the number of analytes I sample changes, so if anyone has any ideas on how to write a macro that can do this by recognizing when the analyte name repeats itself (to know when to start a new set of rows).

View 9 Replies View Related

Transpose Won't Work, Perhaps A Macro Will

Apr 7, 2009

I am sent an email that contains multiple blocks of information.

They're broken down like this:

Title
Link
ID
Department
Date
Status
Value
News

There are about 100 of these blocks of text in the email, and some of them are duplicates. What I'd like to do is copy them into Excel, and run a macro which separates them into the appropriate column and removes the duplicates. The problem that I'm running into is some of the blocks have a value (highlighted in red above) and others don't have anything at all. I'm looking for a solution that will be able to evaluate the text and if the cell after Status isn't value, I'd like it to insert a blank cell, a cell that says ignore, or something that will keep the format correct.

View 9 Replies View Related

Transpose Macro Or Function

Sep 25, 2009

What im trying to do below is transpose the data under each name in column A across the corresponding row the name is on. Is there a way, without copy and paste specialing, to make excel recognize the cell being a name (perhaps from the list of all names on sheet 3 (Data Range = "Names") and transpose the data under it so it ends up light the highlighted text in the image below? Im thinking a macro?

View 9 Replies View Related

Macro To Transpose & Duplicate

May 4, 2007

Is it possible to write a macro that does the following:

(1) Select range of cells to be fixed horizontally.
(2) Select range of cells to be transposed vertically (as attached).
(3) Duplicate each row selected in (1) based on the number of cells that has been transposed in (2)

Sample Data B.xls

The result is as attached:

New Format B.xls

As seen above, each row selected in (1) has been duplicated based on the number of cells that has been selected to be transposed in (2).

View 7 Replies View Related

Macro For Copy / Paste As Transpose?

May 15, 2014

see the attached file below. I have monthly precipitation data for yrs 1950-1999. I need to have the data formatted such that the precipitation data is copy/pasted into one single column, going all the way from jan 1950 to feb 1950 (next row) to mar 1950 (next row).....dec 1999 (last row). On occasion, I will also have data for shorter/longer time periods. the macro code I'd need to take this data and line it all up into one single column?

View 7 Replies View Related

Transpose Data With Macro And Add One Column

Feb 18, 2014

I got to transpose the data from sheet "Data" into new sheet "Actual". Now One row has been added on the top (R1) to categorize the whole data in terms of Major and minor.

modify the macro which add one column (K) with header as "Error type" in sheet "Actual" and then display the above two parameters.

I want exact type of data in sheet "Actual" from raw data sheet "Data"

View 3 Replies View Related

Modified Transpose Function OR Macro?

Oct 14, 2009

I am trying to perform the following, but am unsure if a macro is needed, or if there is a way to do it by possibly modifying the transpose function. Here are the steps required:

1 - copy a selected range of cells from a single row in one worksheet (i.e. D3:AE3).

2 - paste the data from those cells into a selected range of cells from a single column (i.e. A1:A14) in a separate worksheet BUT skip the cells from D3:AE3 that were blank instead of having them create blank cells in the column of the target worksheet

3 - Need to maintain a reference back to the original row of cells so that if their values change (i.e. D3 from source), the corresponding values in the new column (i.e. A1 in target) are auto updated.

View 5 Replies View Related

VBA Macro - Selective Copy And Transpose

Jul 25, 2012

I have a spreadsheet which contains many rows (>3000) and a few columns.

I would like to make a macro that will copy specific cells depending on the number from another cell (from the first column) and then transpose the corresponding values. My current problem is that the values I'd like to copy (countries) are on a row (horizontal) and I need it on a column (below "Countries") next to the car models (in black: the current sheet, in red: my target):

Number of Countries
Cars
Countries

A
FJ

A
UK

[Code] .......

Is it be possible to create a macro that transposes automatically the countries from the right into the third column (like 90 degrees against the clockwise direction)?

View 5 Replies View Related

VBA Macro To Transpose List To A Row On Different Sheet

Mar 21, 2014

I have the below VBA but am getting a 1004 error and when I step into each line I do not see the macro populating the cells as designed. The purpose of the macro is to take a list from the "EmployeeBillableHours" worksheet select the entire list s.tarting from row 2, then goto to "Client" worksheet, then starting at cell J6 paste each item on the list and then move 4 cells to the right and paste the next item on the list to the end.

Code:
Sub ClientListtoClientTab()
Dim lr As Long: lr = Worksheets("EmployeeBillableHours").Range("H" & Rows.Count).End(xlUp).Row

[Code].....

View 1 Replies View Related

Transpose Macro For Erratic Data

Jun 23, 2007

Need macro that will transpose data from rows to columns

TAN KOON TECK
64 JALAN SEMBILANG DUA KAW 7
OFF JALAN TELOK PULAI
**blank**
**blank**
41100
KLANG
**blank**
next entry

There are blank rows in between the address, and 1 blank row between new entry.

Furthermore, the blank rows between the address are not consistent

e.g.
AZAKI B ISHAK
PT 26 PEKAN JELAWAT
**blank**
**blank**
**blank**
16070
JELAWAT

I am wondering if there is a code that could execute a macro to transpose such data automatically instead of me transposing it one by one

View 9 Replies View Related

Create A Macro To Transpose Data

Jan 28, 2008

I have a spreadsheet, with over 9000 line items. And to give you guys an idea, these items are products that we carry at our stores (convinience stores). Most of our items are sold to the customer by eaches or unit, but we do carry beverage items which can be sold several ways (single can, 6pk, 12pk, 24pk etc). Anyway, on the spreadsheet, these sell units(single, 6pk, 12pk etc) have their own line, per each sell unit. I want them to be all in one line. Heres what it looks like:

I want columns F, and G's values be transposed to the first line, after the RET column. Like this:

View 9 Replies View Related

Macro To Transpose Data And Repeat Itself

Nov 27, 2008

I have spreadsheet which consists of two columns of data.

Column A contains a gerneric heading/group and Column B details some results for each heading/group; these results cover between 16 to 40 rows.

Basically, I need to transpose each heading/group's rows of results data into the same row as the heading/group row; then delete the rows where the results data was copied from; then I want to go to the next heading/group in Column A and repeat the process - transposing the results into a row.

I have written a simple macro which seems to work intermittingly - one which doesn't repeat or loop though. Sometimes I get the Runtime Error 1004 and sometimes the macro overwrites the transposed row if the results are listed over 16 rows.

My draft macro code is as follows:

Sub Transpose()
'
' Transpose Macro
' Macro recorded 27/11/2008 by Hunter
'
' Keyboard Shortcut: Ctrl+p............

View 9 Replies View Related

Macro For Paste Values And Transpose

Mar 11, 2009

I want to make a macro that I can copy a column of numbers, select a cell... then run the macro - which will transpose them and paste the values.

I've tried to make this by Recorder... but I keep getting an error.

Run-time error 1004
Paste-Special method of Range class failed.

I've seen other people in the past post this questions, but it seems noone has solved it!

Sub Transpose()
'
' Transpose Macro
' Macro recorded 11/03/2009
'
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
End Sub

View 9 Replies View Related

Macro: Transpose 1 Column Into 3 Columns

Aug 24, 2006

I am trying to transpose 1 column into 3 columns:

Example:
1
2
3
4
5
6
7
8
9
ect...

into

123
456
789
ect...

Here is the code I have:...................

View 4 Replies View Related







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