Over 1 million tech questions and answers.

XLS Macro to move data to columns, delete empty rows, delete duplicate columns

Q: XLS Macro to move data to columns, delete empty rows, delete duplicate columns

I started out with the two previous posts about macros (post 1, post 2) that move rows into columns, but I'm having a little trouble getting it to work for what I need. First, I'm trying to do a data merge into Word, but in order to get the data merge to work/look the way I want it to, I need the XLS data to be in columns.

I'm attaching an example file that has one worksheet of the sample data (though my "real" data has about 300 rows) and the other worksheet is an example of the outcome I need.

Is there anyway I can do what I need? Exporting into another file or another worksheet within this file would be fine.

Thanks in advance.

RELEVANCY SCORE 200
Preferred Solution: XLS Macro to move data to columns, delete empty rows, delete duplicate columns

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: XLS Macro to move data to columns, delete empty rows, delete duplicate columns

Read other 11 answers
RELEVANCY SCORE 212.4

I have been working on a project for a few weeks and I could really use some help. I'm attaching a dummy file (example1) that is an example of how my XLS comes to me each month.

I need a macro that leaves me with the second file (example2). If the macro could just grab the Student name, then put the following info for each student all on 1 row, that would be perfect:

Child's Name | Date In | Time In | Rounded | Time Out | Rounded

Sometimes for some reason there is a student with a (Cont) after their name. I need that row deleted and just pull the times up onto the same row as other times. The example2 file might make sense.

Here are the steps I am doing manually that I hope a macro could do for me!
Un-merge cells: B10
Un-merge any cells with a child's name such as Doe, Jane
Delete any row with a childs name that has (Cont) after it such as Doe, Jane (Cont)
Delete rows: 1-9
Delete columns: A, C, F, H, I, J, M, N, O, and P
Manually move all info up to one row per student
Delete all empty rows below
See example2 as the completed file but I only moved a few of the rows per student for the sake of time.

Is there any way to do all of this with a macro?
 

A:Solved: XLS Macro to delete specific rows/columns and move columns to rows.

Read other 16 answers
RELEVANCY SCORE 167.6

I'm a novice excel VBA macro user. I've manipulated already created macros, but I'm not sure how to create one from scratch. Can someone please help me create a macro.

I'd like to move the values highlighted in yellow to the cells highlighted in green...and then delete any rows that are empty.

Thanks in advance for the help.
 

A:Solved: Excel Macro to Move Data in rows to columns

Read other 9 answers
RELEVANCY SCORE 166.4

I have data that repeats hundreds of times. I am trying to put each string of data on 1 line.

f-name(A2) initial(B2) l-name(C2) ID#(D2) DOB(E2) Street(F2) company(G2)
City(F3) company#(G3)
State,Zip(F4)
Home Phone(F5)
Work Phone(F6)
Email(F7)

After the data is moved to one line I would like to delete the lines that previously housed the information. The company# can be deleted. I have never worked with macros. I would appreciate any help given.
 

A:Solved: move data from rows and columns to other rows and columns

bump
 

Read other 3 answers
RELEVANCY SCORE 164.4

Hello - I found a previous post on this site that solves (I think) 90% of what I need to accomplish. I have a large spreadsheet that I need to:

Delete column C
Delete all rows without account numbers (Acct numbers will always be in column A)
Delete all rows that have dates in column F other than 00/00/0000.

The code I found on this site is :

Simple vba code:
lastrow = Range( "A65536").end(xlup).row
For y = LastRow To 2 Step -1
If Cells(y, x).Value = "" Then Cells(y, x).Delete shift:=xlUp
Next y
Set the range to the the column desired, in this case "A"
Lastrow checks the last row and starts from the end up.
Removes all empty rows entirely up to row 2 because row 1 generally is the header
Happy coding
Can anyone help me?
 

A:Excel 07 MACRO to delete rows/columns w/ criteria

Delete all rows without account numbers (Acct numbers will always be in column A)Click to expand...

If there are no acct numbers, will there be

blanks ""
Space " "
dash -
other ??
Can you provide a sample with dummy data so we can see the data structure?
 

Read other 1 answers
RELEVANCY SCORE 164.4

Hello - I found a previous post on this site that solves (I think) 90% of what I need to accomplish. I have a large spreadsheet that I need to:

Delete column C
Delete all rows without account numbers (Acct numbers will always be in column A)
Delete all rows that have dates in column F other than “00/00/0000”.

The code I found on this site is :

Simple vba code:
lastrow = Range( "A65536").end(xlup).row
For y = LastRow To 2 Step -1
If Cells(y, x).Value = "" Then Cells(y, x).Delete shift:=xlUp
Next y
Set the range to the the column desired, in this case "A"
Lastrow checks the last row and starts from the end up.
Removes all empty rows entirely up to row 2 because row 1 generally is the header
Happy coding
Can anyone help me?
 

A:Excel VBA Macro to delete rows/columns with criteria

To delete column c, use:
Code:
Columns("E").EntireColumn.Delete
Delete all rows without account numbers (Acct numbers will always be in column A)
and
If Cells(y, "A").Value = "" Then Cells(y, "A").Delete shift:=xlUp

Use:
Code:
lastrow = cells(rows.count,1).end(xlup).row
For y = LastRow To 2 Step -1
If Cells(y, "A").Value = "" Then Rows(y).EntireRow.Delete
If Cells(y, "F").Value = 00/00/0000 Then Rows(y).EntireRow.Delete ' Delete all rows that have dates in column F other than 00/00/0000.
Next y


 

Read other 1 answers
RELEVANCY SCORE 157.6

Hello,
I've long used a simple macro on various spreadsheets to reset the last used cell:

Sub Reset_Range()
Application.ActiveSheet.UsedRange
End Sub

This works fine, but prior to using it, I would have to press CTRL + End to see where the current last cell is, then manually delete any blank columns and rows appearing at the end of the data, by selecting them by their column letters / row numbers, right clicking and selecting Delete. Failure to do this would mean the macro didn't successfully correct the last used cell.

I would like to add this stage into the macro.
I found some code on the web that allegedly removes all blank rows and columns, so I pasted it at the top of my existing macro:

Option Explicit

Sub DeleteBlankRows()
Dim Rw As Long, RwCnt As Long, Rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On Error GoTo Exits:
If Selection.Rows.count > 1 Then
Set Rng = Selection
Else
Set Rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row()))
End If
RwCnt = 0
For Rw = Rng.Rows.count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(Rw).EntireRow) = 0 Then
Rng.Rows(Rw).EntireRow.Delete
RwCnt = RwCnt + 1
End If
Next Rw

Exits:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Sub DeleteBlankColumns()
Dim Col As Long, ColCnt As Long, Rng As Range

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

On ... Read more

A:Solved: Excel 2003 macro to delete blank columns & rows, then reset range

Read other 6 answers
RELEVANCY SCORE 148.8

I have a flat file that when I read it into excel it populates column 1 of each row with data.
This data is actually a series of 5 fields that I need to have in columns and rows, that is,
column a1 thru a5 I need placed in a1, b1,c1,d1 and e1 followed by
column a6 thru a10 placed in a2,b2,c2,d2 and e2.
The data is balanced, that ism there is data in each of the 5 fields for a "record".
Any help would be appreciated.
THanks
 

A:Solved: excel macro to move columns to rows

Read other 8 answers
RELEVANCY SCORE 144.8

I have a very large spreadsheet (i.e. too big for pivoting and transposing wouldn't work) which looks something like this:
Col. ACol. BCol. CCol. DCol. ECol. FCol. GEntry1Info124562131654632435435Entry2Info150256874654242454654Entry3Info2221333333336546548463
I want it to look like this (i.e. flip Col.C through Col. G and line it up with Entry1, then do the same for Entry2, etc.):

Col. ACol. BCol. CEntry1Info12Entry1Info456Entry1Info2131Entry1Info6546Entry1Info32435435Entry2Info150Entry2Info25Entry2Info6874654Entry2Info242Entry2Info454654Entry3Info222Entry3Info133333Entry3Info333Entry3Info654654Entry3Info8463

Please help!
 

A:Move Data from Columns to Rows (w/o pivot)

Read other 11 answers
RELEVANCY SCORE 140.8

"Macro Creation Request for Moving Data from Rows to Columns in a Non-Conventional Way"

Hello, all! Hopefully somebody can help me with the following problem (please see the attached document).

Presently, I have an Excel 2010 document containing variables 1-6 per country in rows for label 1 through label x. However, I need the table to contain the country level information as columns for each label so that there is only one row per label. For example, instead of row 1 containing label 1 argentina variables 1-6 then row 2 containing label 1 arab emirates variables 1-6, I need the table to contain row 1 label 1 columns argentina variable 1, argentina variable 2, etc...then arab emirates variables 1, arab emirates variable 2, etc.

Also, the countries in the table that do not have information for a given label fall at the end of the list for each label instead of in alphabetical order, but they need to be placed in alphabetical order when "columnized." I have provided the arab emirates as an example. Alphabetically, it comes before Argentina, but in the list it comes after because it did not contain information for those given labels. Since information per country will vary from label to label, the countries must remain in alphabetical order in the columns regardless of whether information is provided or not per label.

Is there a macro that may be written for this?

Thank you so much, in advance, for taking the time to read my post! Happy day!
 

A:Macro Creation Request for Moving Data from Rows to Columns in a Non-Conventional Way

Read other 14 answers
RELEVANCY SCORE 137.6

I need a visual basic macro for excel 2002 that will do the following:

From sheet1:ColumnA
Select rows until sheet1:columnA value does not start with REC (as the no of rows is unbalanced, sometimes 7 rows, sometimes 10 rows, sometimes 8 rows, etc)
copy selected rows and transpose to sheet2:columnA to however many rows were selected

Repeat until end last row
My real table has 30000 rows and the rows are unbalanced

I am attaching a test file.

Please help me and thank you for your support and time.

Tech Support Guy System Info Utility version 1.0.0.2
OS Version: Microsoft Windows XP Professional, Service Pack 3, 32 bit
Processor: Pentium(R) Dual-Core CPU E5300 @ 2.60GHz, x86 Family 6 Model 23 Stepping 10
Processor Count: 2
RAM: 2047 Mb
Graphics Card: ATI Radeon HD 4300/4500 Series, 1024 Mb
Hard Drives: F: Total - 99998 MB, Free - 30856 MB; G: Total - 205236 MB, Free - 153405 MB; H: Total - 305168 MB, Free - 180367 MB;
Motherboard: MICRO-STAR INTERNATIONAL CO.,LTD, G31M3-L V2(MS-7529)
Antivirus: None
 

A:Visual Basic Macro for Excel 2002 columns to rows in new sheet using unbalanced data

Can you also attach a second workbook showing how the data should appear after the macro is run?

Regards,
Rollin
 

Read other 3 answers
RELEVANCY SCORE 136

I export a csv file to my computer which I need to import into my Access database. It is about 73 columns wide and I need to delete almost half of the columns. They are not used in the database.

I have been doing it manually, but that would seem to me to be something that could easily be automated.....if you know what to do. I've tried but without any knowledge of the procedures, it is not easily learned from a tutorial. My first attempt worked like a charm but I screwed up trying to save it; I have not been able to duplicate since.

Can someone help? It will be greatly appreciated.

THANK YOU !!
 

Read other answers
RELEVANCY SCORE 131.6

I am trying to write a macro that compares cells in column A from sheet1 to cells from column A in sheet2 and if the values from sheet2 are not in sheet1 then I want to delete the entire row from Column A in sheet2.

for example

In sheet1, column A has a list of id no's (no duplicates) and in sheet2 Column A has list of id no's (with duplicates). I want to delete every row in Sheet2 Column A that is not in Sheet1 Column A.

Keep in mind that both sheets have several columns.
Also there is about 5000 rows in sheet 2

Any help would be greatly appreciated.

Thanks
 

A:Excel HELP macro to Delete Row Based 2 columns from 2 sheets

Welcome to the board.

While I understand each part of your post (I think), some parts seem to contradict others. So here's what I did.

In Sheet1!A1:A5 I entered 1 -- 2 -- 3 -- 4 -- 5.

In Sheet1!A1:A6 I entered 1 -- 2 -- 6 -- 3 -- 4 -- 5.

Then I wrote some code that removes the third row from Sheet2, since it contains 6 in column A which is not in col A of Sheet1.

How the code works is:

(i) inserts a new column A on Sheet2

(ii) enters a formula in new column A Sheet2, e.g.:

=MATCH(B1,Sheet1!A:A,0)

which returns a number if there's a match in column A Sheet1, #N/A if not

(iii) deletes any rows on Sheet2 with #N/A in column A

(iv) deletes column A on Sheet2 (redundant).

Obviously if this isn't what you need don't use it, post more info instead.

HTH

Sub test()
Sheets("Sheet2").Range("A1").EntireColumn.Insert
x = Rows.Count
y = Sheets("Sheet2").Range("B" & x).End(xlUp).Row
Sheets("Sheet2").Range("A1").Resize.FormulaR1C1 = "=MATCH(RC[1],Sheet1!C,0)"
Sheets("Sheet2").Range("A1").Resize.SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Sheets("Sheet2").Columns(1).Delete
End Sub
 

Read other 1 answers
RELEVANCY SCORE 128.8

I need to delete multiple columns within an Excel worksheet based on the headers in the first row. What would be the best way to perform this operation?

I have been playing around with the code below, but I feel as though there could be a better way.

Sub test()

Dim myArr As Variant

myArr = Array("Test1", "Test2", "Test3")
If Range("C1").Value = myArr Then
Columns("C").Delete shift:=xlToLeft
ElseIf Range("D1").Value = myArr Then
Columns("D").Delete shift:=xlToLeft
End If

End Sub
 

A:Solved: Excel Macro -> Delete Multiple Columns Based on Criteria

Read other 16 answers
RELEVANCY SCORE 126

Anybody ever encountered this situation or have any ideas?

One of our users has a multi-tab Excel (vers. 2002, SP3) workbook with several columns containing pricing and taxes on products we handle. When she prepares to print the worksheet, she hides some of the middle columns so that only the data in the first and last columns will print out. The issue is, as soon as she unhides the hidden columns, certain data fields get wiped out... deleted. Then, she has to retype them. There are no macros or VBA scripts programmed in this workbook, and no wierd formulas I've seen that would potentially do this. Is this possibly an Excel bug???
 

A:Hiding Columns Appear to delete data in hidden fields (?????)

Sounds like a user bug. I won't believe it's exactly as you describe without seeing the file.

 

Read other 2 answers
RELEVANCY SCORE 124.4

Hi,

I have data in the following row format:

0 4591 5 4491 10 4461 15 4441 20 4321

The data are X and Z points so the above example shows 5 pairs of data. I would like to place these pairs of data sequentially in two columns where one column has all the X's and the other has the related Z's. I have many, many rows of similar data that I need transferred into two columns.

0 4591
5 4491
10 4461
15 4441
20 4321

Could anyone help me out with a macro that can do this for me?

Thanks very much in advance.
 

A:Solved: Excel Macro - Rows to Columns

Welcome to the board.

Let's say the example pairs are in A1:J1.

With A1:J1 selected,

Sub test()
For Each Cell In Selection
If Cell.Column Mod 2 = 1 Then
Range("A" & Rows.Count).End(xlUp).Offset(1) = Cell.Value
Else
Range("B" & Rows.Count).End(xlUp).Offset(1) = Cell.Value
End If
Next Cell
End Sub

gives you actual pairs in A2:B6.

So, what do we need to adjust to make it compatible with your "many, many rows of similar data" scenario?
 

Read other 3 answers
RELEVANCY SCORE 123.2

Hi Again

CodeLexicon gave me a code that worked very well in hiding column A only. Is it possible to make the macro hide all columns with all zeros?? Also I tried to change CodeLexicon's macro to do hide all rows whose columns have zeros but without success. I tried to make the code generic.

Attached is a workbook with the 2 macros. Test the macro that I did out and see that it only hides row A only.

Mario
 

A:Solved: Macro to hide all rows whose columns have zeros

Read other 6 answers
RELEVANCY SCORE 123.2

Hi, I'm new to macro. Hope you can help me with my problem. I've been stuck with this for a week with no result.

I have this table:

001 002 003 004
111 115 119 123
112 116 120 124
113 117 121 125

Let's say, the first row are the StoreID and all other digits below them are the ProductID

I want to convert this table into something like this:
001 111
001 112
001 113
002 115
002 116
002 117
003 119
003 120
003 121
004 123
004 124
004 125

I haven't made any macro before in MS Excel and this one is really getting the breath out of me.

Hope you can help me please.. I'd really appreciate it.

Thank you in advance!
 

A:Help with Macro in excel - Convert Rows to Repeating Columns

is this just a "one off" thing or do you have many sheets to convert? Because for one or a few sheets, it is not a lot of work to do manually...probably take a few minutes ( I 'll explain if you need)

to automate I can do it, but I need to build some loops and references to refer to the columns and ranges to move to a new list...it's a bit of work.

Please confirm also if it is only 4 columns, and or if the column count varies
 

Read other 1 answers
RELEVANCY SCORE 123.2

In my work environment we are writing test cases and for the test steps we use a word table. Its the same 4 columns so we can load them into the Quality Center environment. One issue we had was our review tool doesn't support word but does text documents. The format of the table was kind of messy when we saved as a text document in the sense it would lump all the stuff together with no clean breaks. As a solution people making every other row and column blank so there would be a break in the text format. Once the review was complete you would have to then go back into that word doc and delete all the blank rows and columns to load into Quality Center. Not hard but time consuming with the number of test we would deal with. I wrote a quick macro that would delete the header in the table plus all the blank rows and columns.

Now what we have is we are re using these test and adding to them. Currently we are pulling them out of quality center, adding the blank rows and columns back in, adding our updates, sending back out for review, deleting the blank rows and columns (macro does this), load back into Quality Center.

There may be a better way to go about this but what I am after is this. A macro that will insert a blank row and column every other row and column. I have been off and on playing with this for a few days but really have gotten no where with it. Posted on a few non technical forum and they suggested posting on this site for some help. I tried searching and did... Read more

Read other answers
RELEVANCY SCORE 121.2

In Sheet one I have some raw data which in sheet two I have picked out the useful pieces and have then input into out work forms. The problem is that I need to fill the form across (column) but in the sheet that it is referenced from I need it to take the data from the row

EX.

sheet one
....a.. b. c
1 a1 b1 c1
2 a2 b2 c2
3 a3 b3 c3

I need to find a way to automatically get it to do this referencing sheet 1 in column A and then filling to the right but actually having it go down the rows in sheet 1
sheet 2
...a.. b ..c
1 a1 a2 a3
2 b1 b2 b3
3 c1 c2 c3
the idea is that when the software exports to sheet 1 we will be able to print sheet two in the form we need it without having to make any changes. I know my explanation is a little crude but if someone could help me I would appreciate it. thanks.
 

A:Excel problem. Fill columns I need to move across rows in another sheet

You could use:

=OFFSET(Sheet1!$A$1,COLUMN()-1,ROW()-1)

in Sheet2!A1, then replicate that down an across (pro forma style).

Would that help?
 

Read other 1 answers
RELEVANCY SCORE 120

Hey,

I have imported sales data in excel. The information lists in along two rows with mutliple columns. I need the information to be in one row under various headings. I have attached a link to a sample to explain this properly. I think I need to use a macro but I don't understand enough about them to make it work.

Any help would be much appreciated.
Cheers
https://docs.google.com/spreadsheet/ccc?key=0Av0-qDbMrH0rdHJWaWlReXd4RXptWlpwME50UTBsclE
 

A:Moving data from columns to rows

Read other 11 answers
RELEVANCY SCORE 118.8

Hi,

I need a macro to find the duplicate values in Invoice # column and replace the duplicate values in Invoice Amount and Discount Taken columns with nothing. A macro that I can change Example A to Example B like below. Note: The actual spreadsheet contains over thousand rows and over 15 columns. So there are multiple columns that I need to do the same. It will be fantastic if I can have a macro that can work with multiples columns at once by simply adding additional columns to the macro.

I am a newbie in macro so please provide a complete macro from start to end sub otherwise I may not know how to use it. Note: A simple adjustment could throw me off if the instruction is not step-by-step. Thank you very much.

Example A (From This)

Company Name
PO #
SO #
Invoice #

Invoice Amount
Invoice Date
Apply Amount
Discount Taken
Apply Date
Check #
Bibiba
BL123 L1
ORD245
INV101
$ 300.00
3/12/2011
$ 297.00
$ 3.00
4/12/2011
234
Bibiba
BL123 L2
ORD326
INV212
$ 5,000.00
5/23/2011
$ 2,570.00
$ 30.00
6/23/2011
392
Bibiba
BL123 L2
ORD326
INV212
$ 5,000.00
5/23/2011
$ 400.00
$ 30.00
6/23/2011
RTN123
Bibiba
BL123 L2
ORD326
INV212
$ 5,000.00
5/23/2011
$ 2,000.00

6/30/2011
399
Bibiba
BL123 L3
ORD326
INV367
$ 200.00
5/25/2011
$ 198.00
$ 2.00
6/25/2011
401
Bibiba
BL123 L4
ORD326
INV368
$ 200.00
5/29/2011
$ 200.00

6/29/2011
534
Kilik
287
ORD401
INV234
$ 400.00
4/1/2011
$ 396.00
$ 4.00
4/15/2011
1020
Kilik
287
ORD402
INV249
$ 3,000.00
4/12/2011
$ 590.... Read more

A:Macro to find duplicate values in columns and replace with nothing

Read other 6 answers
RELEVANCY SCORE 118.8

Hi all,

I have a requirement. Consider there is an excel sheet with values in several columns. Some of the columns inbetween are empty. So i have to somehow highlight the empty columns.
Note: All the columns have headings in the first row.
It is ok even to get the headings of the empty columns in seperate sheet.
Help me out guys.

Thanks in Advance,
Sughesh.
 

A:How to find Empty columns in Excel using Macro

will conditional format workto highlight blank cells if so

goto conditional format- click on cell A1 and then hightlight the range - if you want all the spreadsheet - click on the box between the columns and rows
then in conditional format
type

=A1=""
then set a format fill colour

heres a macro to remvoe blank columns
http://www.mrexcel.com/archive/VBA/11225.html
but may not work if you have a heading - as thats not a blank column

if you could load a dummy spreadsheet as an example- others here with more macro experience should be able to help
 

Read other 3 answers
RELEVANCY SCORE 118.8

Hi

(A)
does anyone know how to search for the text content of a cell through a column and then return the value/content of the cell of a certain other column of the same row where the text content was found?
e.g.

A1 B1 C1 D1
2 013004T XY
3 092004TP YZ
4 012004TP ZY
5 052004P YX
6 092004TP YY [FORMULA]
7 072004TP ZZ
>>FORMULA: "Look for string '092004TP' in column A (other than in row 6) and return the value of that row in column C (C3=YZ)."
NOTE: There might be more than 1 row with the string '092004TP' in column A - I would need the sum of all results in column C where the FORMULA finds this string.

(B)
I also have a similar problem with another worksheet:
It would require a formula which searches through a whole column for certain names, e.g. "Donna", and then returns the value of another column in the same row where it found "Donna"; the value, again, is not in a column directly adjacent to the column where "Donna" would be found.
This is fairly identical to problem (A), except that the search string would be unique (only 1 result), and it doesn't have to take into account (and disregard) if the string was found in the same row of the FORMULA (the search column would be in a different worksheet from the FORMULA worksheet).

Thanks for any suggestions!

Cheers
from New Zealand

Andreas
 

A:EXCEL Searching Data in Rows/Columns

Read other 7 answers
RELEVANCY SCORE 118.8

I have data in columns - starting A2 - P2 (and another set of data from A3-P3)

I have various formulae using that data, starting in A20-A40.

I wish to include the second 'row' of data (the A3-P3) into a second set of formulas from B20-B40.

Filling 'right' doesn't work, because, if the formula in the A column is something as simple as "=A2+C2+D2", when I fill right, the formula in the B column is "=B2+D2+E2" - when I NEED it to read "=A3+C3+D3"

I realize there is probably a simple workaround for this - in fact, I COULD just rekey the formula...the reasons I don't are 1) the formulas are more elaborate than I listed, 2) there will be many more rows of data (at least 15 more) - so, rekeying the formula for EACH row of data would be time-consuming and 3) it will eventually move the formulas off the same 'visible area' as the data (I'm currently combatting that by having the panes frozen).

Is there any way to accomplish what I'm trying to get done?

Chris
 

A:Creating Formulas in rows from Data in columns

Read other 9 answers
RELEVANCY SCORE 118.8

Hi,
Is there anyone who could help me with one macro? This is the format of the data:

Picture1

And this is what I would like to have at the end:

Picture2

So basically I need a macro that would copy column D&E and put the data below B&C, also copying tasks respectively. and then the same for all other columns. The problem is that the number of columns varies... sometimes it's 30, sometimes it's much more.

Thanks in advnace
 

A:Solved: macro to move columns

Welcome to the forum fenrost.

Is there always going to be 7 tasks?
 

Read other 1 answers
RELEVANCY SCORE 118.8

I was hoping someone could help me write or find a macro that would take two columns and move it to column A under the previous data and repeat for all columns. Basically user information is located in pairs of columns (typeofvalue, value). So column A and B are for user1, column C and D are user2, column E and F are user3, etc. Bonus if I could have a space in between each once moved.

Current:


HTML Code:
A B C D
User1First User1Last User2First User2Last
User1ValueTypeA1 User1ValueB1 User2ValueTypeA1 User2ValueB1
User1ValueTypeA2 User1ValueB2 User2ValueTypeA1 User2ValueB1
Desired:


HTML Code:
A B
User1First User1Last
User1ValueTypeA1 User1ValueB1
User1ValueTypeA2 User1ValueB2

SPACE/EMPTY ROW

User2First User2Last
User2ValueTypeA1 User2ValueB1
User2ValueTypeA1 User2ValueB1
Each user has 47 rows of "values", for the example I just did two rows worth.

A:Excel Macro to move columns to 1 row after each other.

I did a quick google and found this. It worked for you?

Excel macro to move columns to 1 row after each other

Read other 2 answers
RELEVANCY SCORE 117.6

We have a survey package which is great, but it out puts the questions numbers over the columns and each new row is a response, however our calculations are setup for the question numbers to be the rows and the responses are the columns. Is there any way to swap these values around in this fashion? Thanks for any help
 

A:Excel: swapping columns and rows layout of data?

Howdy. You can copy the data, then Paste Special > Transpose
 

Read other 2 answers
RELEVANCY SCORE 117.2

I have a dump from a text file in two single columns. The first column is a block of 55 labels that repeat for every group of data, the second is the data for 110 transducers. Every transducer produce 55 single values (numerical or status(text)). I need to order this two columns dumps into a table. Where the first column are the labels and the rest 110 column the corresponding data one column per transducer.
How can we do this? Can any body help me?
 

A:Excel macro to move two Columns into a table

Hi and welcome to TSG!

Can you upload a sample of the textfile? I just need a few lines. It doesn't have to be real data. Just want to see the data structure.If you can provide this, I'm sure we will solve the problem.

Jimmy
 

Read other 1 answers
RELEVANCY SCORE 116.4

..... and I want to convert the lot to 2 decimal place numbers.

Excel has several methods, but none seem to work for conversion in bulk. Working on futher cells from a suitably formatted one does not work.

Is the only way to solve my problem to manually enter the numbers elsewhere, clear the original cells, and then cut and paste back to the original cells?

Help please!

A:Number data in 10 columns and 25 rows pasted from other source is text

Need more info about the source text file. When opened in Word and Show All (the paragraph mark) is enabled, what does the text file look like?

See this thread from a couple years ago and see if it gives you any ideas:

Please Help - Data in Text File to be arranged in Excel in a specific

Regards,
GEWB

Read other 3 answers
RELEVANCY SCORE 113.6

I have found a few posts here and there about how to move columns around but nothing quite what I m looking for.

So I get these data dumps from a vendor weekly , column headers are always the same but they are always in different order (strange I know)

Example

Email | Phone | Last Name | First Name

I would like to have a macro cut/paste entire rows and put in a specific order

First name |Last Name| Phone| Email

Any help would be great! Thank you!
 

A:Excel 2007 – Macro (newbie) - Move Columns bases on name question

How many total columns are we talking about? Is it possible to post a sample workbook with any sensitive data removed so I can see the structure.

Regards,
Rollin
 

Read other 3 answers
RELEVANCY SCORE 110.8

What would be the best way to find a specific Column Header, and copy the data found in the column until the column header is found again?
 

A:Solved: Excel Macro Copy Data From Columns

Read other 8 answers
RELEVANCY SCORE 110.8

Hi,

I am trying to classify and code my inventory items in excel.
My classification sheet contains data in columns which I would like to copy to rows in another excel sheet.
Here is the sample data in ACMOTORS-ATTRIBUTES sheet:

The above sheet has to be converted to columns as in Item_Classificatios_Form sheet :

Can anybody assist me in writing a macro for the same.

Thanks,
mihaufo
 

A:Solved: Excel Macro to convert Rows from one sheet to columns in another sheet

Read other 8 answers
RELEVANCY SCORE 109.2

Hi,

I was hoping somone could please help me out.

I have an excel spread sheet with a lot of data in columns which i need to convert into rows. The problem is one column contains over 10,000 rows of data... thus making it too hard to transpose manually.

I have attached a copy of the spreadsheet (condensed version) and was hoping someone had some suggestions on how i can achieve my desired outcome.

Thanks very much,

Ray
 

A:Move alot of Data from Columns Excel 2010 - too big to transpose

I'm sorry to say that this is pretty much impossible as far as I can tell because there simply isn't enough consistency in your layout. If all rows were there and in order it would be easy enough.
As they aren't, one would need to inspect the titles to put them in the right place, but your titles don't always match either. For example your second "Telephone" entry is actually titled "Clinic Mobile", which the computer justly sees as very different indeed.
I'll try to help if I can, but you'll need some point of consistency if you want computers to automate a process. If it helps, here's a copy of my spreadsheet that inspects the titles (on sheet 2). If there are only a few problematic titles it might be easier to correct them manually (or use search and replace).
 

Read other 3 answers
RELEVANCY SCORE 108

I do a weekly report that I base on results that I get with search done in Internet Explorer. I can then cut and paste these results into an Excel spreadsheet, but I need to delete most of the information (whole rows) and reorganize the remaining rows by taking the even numbered rows and putting them into the B column, then delete that Row as well. I have included some images that should help. A few more items:

1.)The information that I paste into the excel spredsheet may contain up to, but no more than 350 lines from the original copying source in Explorer (I'm not sure how many lines that translates into Excel)

2.) After every 20 records in Explorer there is a "top" link that gets copied, which needs to be taken into consideration when deleting the extra rows entirely

3.) Another way to look at this is that I ONLY want to keep the rows that have the 7 digit number a space then 2 more digits, as well as the rows that contain the price with the $ For instance the first record in my example I ONLY kept 8055312 11 & $70,000. The price will ALWAYS have a $ and the first set of number will ALWAYS have 7 digits first, no letters.

Attached is a jpg that shows the various steps, the last screen shot has a few of the cells highlighted in the upper left hand side. Those 6 highlighted cells is all of the information I need, and how I need it presented from the first 3 search records, the MLS number and the price.
Thanks!
 

A:Solved: Excel Question: Removing unwanted Rows, moving some rows to new columns

Read other 16 answers
RELEVANCY SCORE 107.6

i tried looking around for a similar macro that would delete all columns except few based on the name in row 1, but all i could find is deleting rows based on condition, not deleting columns as i need and doing the additional things that i need..

i need this macro to do couple of things, here is the breakdown:
1. remove all columns except few based on row 1 name
2. remove entire row based on a condition
3. rename some titles in row 1
4. reorder the entire sheet based on date in order from oldest to newest

first i'll explain my excel sheet then below is the bits of code that i think is correct, just need to put it together...

this is my excel sheet...to make things easy, i will only write out the names of the columns i would like to keep.

Column A, row 1: Supplier
Column B, row 1: Quantity
Column C, row 1: UOM
Column D, row 1: (blank) --- i also want this column removed, but has no name
Column E, row 1: Destination Type
Column F, row 1: Item
Column H, row 1: Item Description
Column I, row 1: Location
Columns J - L -- remove
Column M, row 1: Subinventory
Columns N - W -- remove
Column X, row 1: Order
Columns Y - AP -- remove
Column AQ, row 1: [] -- yes, this is what its called when exported from the database
Column AR -- remove

1. i need to remove the columns from above. I have a code that will delete the items that i dont need by writing out the column names,
but i want it the other way around so it will delete everything except what i need (this macro below i... Read more

A:Solved: delete all columns except some with particular headers in row 1

any help with this would be great, thanks!
 

Read other 1 answers
RELEVANCY SCORE 106.4

Hi,

I have been working on my resume for acting and I have been trying to figure out how to make all of my columns line up evenly (right now they are set up manually and they are not perfectly straight as a result). Also, I can't seem to get rid of the blank page at the end of the document. I have tried everything from decreasing the font size to deleting the page break. Deleting the page break messes up the columns in my document. I have attached the document below. Please help! I have spent hours trying to figure this out.
 

A:I cannot get the columns to line up and the blank page to delete

Read other 6 answers
RELEVANCY SCORE 105.2

If you check the file attached.

please can you help me in moving data with duplicate names to have the 2nd and 3rd email address moved to a new column and delete the row

need an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.
 

A:Excel Macro to Move data in Rows to column

I'm unable to understand the entire requirement, but for moving the records with duplicate names, I've written few lines. This will cut the duplicate record rows (Consider "Pupil Forename" for removing)from the "Report Data" and will post into "Sheet3". Before running this macro please insert "Sheet3". Let me know your requirement, hope I will be able to resolve it out. Thanks!

Code:
Sub test()
Sheets("Report Data").Select
Dim trow As Long
trow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
For i = trow To 1 Step -1
If i = 1 Then
Exit Sub
End If
If Cells(i, 1).Value = Cells(i - 1, 1) Then
Rows(i).Select
Selection.Cut Destination:=Sheets("Sheet3").Range("A65536").End(xlUp)(2)
End If
Next i
End Sub
 

Read other 2 answers
RELEVANCY SCORE 105.2

I have a excel file like below.

Column1 |Column2|Column3|Column4|Column5|Column6|Column7
Row 1 EEEE 1 2 3
Row 2 4 5 6

I want to move data in row 2 (4,5,6) to Row 1 columns 5,6,7.

Could you help me with an excel macro to automate this as i am working with a large number of records like this in one excel sheet. i.e, i want to select the sheet and move data in every 2nd row to the columns in row above at the end of columns in that row.

your assistance is greatly appreciated.

thank you
 

A:Excel Macro to Move data in Rows to column

Read other 8 answers
RELEVANCY SCORE 104.4

Please Help - ASAP - Thank you in Advance.
(I did also post this under Business Applications, before realizing it should probalby go here - sorry about that)

I'm just STUCK on this one last bit of my code to finish off this macro for a meeting today

I am Copying a Range of data, from a specific sheet on about 36 Workbooks
I need to paste only certain columns from that copied info, into a new worksheet.

Maybe I'm making this sound too difficult... at this point all I need is:

Columns A-E, K, P, S-U, W, AB-AC, AE-AH, AJ-AK, from the "TargetData" (Defined Range) on Customer Targets worksheet of Test1, to Paste into Columns A-S of the Build Worksheet

This should be easy right? I'm having a mind block...
 

A:Excel Macro - Move Specific Columns from Range to New Range - ASAP If Possible

There have been a bunch of views, but not replys or suggestions. Am I not including the right information? Is this not posted in the right place? Can someone please help me?
 

Read other 2 answers
RELEVANCY SCORE 104.4

Ok another challenge.. I wish I could do this myself, but I struggle with the code.

Is there a way to have a macro check the line below it, and if it is a duplicate row, delete it
 

A:Solved: Im sure it can be done.. Delete Duplicate Rows

Read other 16 answers
RELEVANCY SCORE 104

Please Help - ASAP - Thank you in Advance.

I'm just STUCK on this one last bit of my code to finish off this macro.

I am Copying a Range of data, from a specific sheet on about 36 Workbooks
I need to paste only certain columns from that copied info, into a new worksheet.

I think I've attached a sample of the code, and some test workbooks I've been using.

Any advise would be helpful. I know this should be easier than I'm probably making it...
 

A:Excel Macro - Move Specific Columns from Range to New Range

Read other 16 answers
RELEVANCY SCORE 101.2

Simple problem - copy sheet then delete duplicate rows

I have a worksheet that is composed a header row, and columns of data (client call results).
Each client is identified by a unique ID number in column A, a seperate column F has the 'call number, from 1 to x'.
So a client can have a single or multiple rows of data. Each client group of rows is sequenced with call 1 as line 1, call 2 at line 2, etc. etc. as your proceed down the rows.
The entire worksheet is already sorted (with a header row) ascending by client id and call #

What I want to accomplish is this

1 - copy the existing worksheet (CallRecords) to a new worksheet (CallToday)
2 - in the new worksheet, scan down (or up) from first data row (2) to the last row {ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row} and
delete duplicate rows of the Client ID retaining the row with the max call number for that client id.
3 - sort the new worksheet (CallToday) ascending by column (G) date, and column (H) time.

Thanks for your help. I am a newbie to VBA.
 

A:Simple problem - copy sheet then delete duplicate rows

stewartrr said:

I am a newbie to VBA.Click to expand...

Not a problem, since you don't need it for this ; a combination of built-in functions will do.

It's not clear if you have blanks in col A -- if you do, that can be easily fixed.

See the attached. The list of ID #s in col A of Sheet2 was extracted from Sheet1 using Advanced Filter (unique records only). The formulas in col B of Sheet2 pull in the value from col B of Sheet1 (per the last row for each ID #).

Post back if you need help interpreting the formulas.
 

Read other 1 answers
RELEVANCY SCORE 100.8

Hi All,

I have a spreadsheet containing 30 address blocks that I'm trying to clean up. A sample is attached.

All of the data is in column A. Each address block is 6 rows, like this:

Name
Company Name
Street Address
City/State/Zip
Phone number
Email Address

Between each 6 row block is a varying number of additional rows that might or might not contain extraneous information. I want to remove those extra rows leaving only the address blocks.

I'm thinking about counting the "x" number of rows between email address "@" symbols, and then just deleting X-6 rows. But I'm not sure how to accomplish this.

Any ideas how this might be accomplished, or any better suggestions to accomplish the goal?
 

A:Solved: Excel macro/vba to delete rows

"might or might not contain extraneous information"

For Each Cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
If InStr(Cell, "@") <> 0 Then
Cells(Cell.Row - 5, 1).Resize(6, 1).Copy Range("B" & Rows.Count).End(xlUp).Offset(1)
End If
Next Cell

If those are real names and addresses, you should remove your file.
 

Read other 2 answers
RELEVANCY SCORE 100.8

Hi,
can any body suggest me a macro code to delete the blank rows from the worksheets, i have a worksheet in which blank or empty rows are present unevenly. and i want to delete all such blank rows all at once. so please suggest me a code.
 

A:how to delete blank rows in a worksheet by macro?

Hi,
I referenced this utility at MSDN, and tested it.
This may or may not be what you are looking for
============== =====================================
Place the following VBA code in the worksheet you want to delete blank rows from
********************************************************************************
' The following code deletes blank rows from the active worksheet.
Dim Counter
Dim i As Integer
Sub DelRow()
' Input box to determine the total number of rows in the worksheet.
Counter = InputBox("Enter the total number of rows to process")
Activecell.Select
' Loops through the desired number of rows.
For i = 1 To Counter
' Checks to see if the active cell is blank.
If ActiveCell = "" Then
Selection.EntireRow.Delete
' Decrements count each time a row is deleted. This ensures
' that the macro will not run past the last row.
Counter = Counter - 1
Else
' Selects the next cell.
ActiveCell.Offset(1, 0).Select
End If
Next i
End Sub
================================================================================================
The solution can be found at.....
http://support.microsoft.com/kb/110759
**********************************************
You may further be able to programmatically create a form with a button that will automate the process.
Hope this helps
 

Read other 3 answers
RELEVANCY SCORE 100.4

I have formulas that has the data setup in rows (input). The next sheet is formulas that are in columns referencing these individual rows (output). When column A's formulas are dragged to column B, the references change as expected but to Sheet1!B1 rather than Sheet1!A2 (back to the rows). I think I 've done this before, but can't for the life of me remember how. Help me please and I'll be your best friend! (hey, it's the best I can do).

thanks,

drew
 

A:Excel Columns to Rows

Select all the cells involved

Right click anywhere on your selection and from the menu that pops up choose Copy

Click in the cell where you want the list to begin then Right click and from the menu choose Paste special you will now see another menu.

At the bottom right hand side of this menu, Select Transpose.

That's it your list will now run across the page instead of down
 

Read other 3 answers