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.

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

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

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

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)
Home Phone(F5)
Work Phone(F6)

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


Read other 3 answers

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:
Delete all rows without account numbers (Acct numbers will always be in column A)
If Cells(y, "A").Value = "" Then Cells(y, "A").Delete shift:=xlUp

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

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

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

Sub Reset_Range()
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
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
RwCnt = RwCnt + 1
End If
Next Rw

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

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.

A:Solved: excel macro to move columns to rows

Read other 8 answers

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

"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

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
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;
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?


Read other 3 answers

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.


Read other answers

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.


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.:


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.


Sub test()
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
End Sub

Read other 1 answers

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

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


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

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.


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

Read other 6 answers

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

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

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


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:


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

Would that help?

Read other 1 answers


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.

A:Moving data from columns to rows

Read other 11 answers


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 #
BL123 L1
$ 300.00
$ 297.00
$ 3.00
BL123 L2
$ 5,000.00
$ 2,570.00
$ 30.00
BL123 L2
$ 5,000.00
$ 400.00
$ 30.00
BL123 L2
$ 5,000.00
$ 2,000.00

BL123 L3
$ 200.00
$ 198.00
$ 2.00
BL123 L4
$ 200.00
$ 200.00

$ 400.00
$ 396.00
$ 4.00
$ 3,000.00
$ 590.... Read more

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

Read other 6 answers

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,

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

then set a format fill colour

heres a macro to remvoe blank columns
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

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?


A:Creating Formulas in rows from Data in columns

Read other 9 answers


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?

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.

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!

from New Zealand


A:EXCEL Searching Data in Rows/Columns

Read other 7 answers

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


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


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

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.


HTML Code:
User1First User1Last User2First User2Last
User1ValueTypeA1 User1ValueB1 User2ValueTypeA1 User2ValueB1
User1ValueTypeA2 User1ValueB2 User2ValueTypeA1 User2ValueB1

HTML Code:
User1First User1Last
User1ValueTypeA1 User1ValueB1
User1ValueTypeA2 User1ValueB2


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

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

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.


Read other 1 answers

..... 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


Read other 3 answers

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)


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.


Read other 3 answers

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


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.


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

Read other 8 answers


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,


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

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.

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

Read other 16 answers

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


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

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!

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
Selection.Cut Destination:=Sheets("Sheet3").Range("A65536").End(xlUp)(2)
End If
Next i
End Sub

Read other 2 answers

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

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

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

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

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

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?

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")
' Loops through the desired number of rows.
For i = 1 To Counter
' Checks to see if the active cell is blank.
If ActiveCell = "" Then
' Decrements count each time a row is deleted. This ensures
' that the macro will not run past the last row.
Counter = Counter - 1
' Selects the next cell.
ActiveCell.Offset(1, 0).Select
End If
Next i
End Sub
The solution can be found at.....
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

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:

Company Name
Street Address
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


I do not know much about excel so i need your help.
Attached is a screenshot. Please examine it a bit to know which rows and columns the data responds to.

Now that you done that heres the question:

How can i switch the dates to the top row and the exercises to where the dates are but keeping the readouts of the weight measurements to its corresponding labels?
I looked over it and it seemed like i had to do it manually but i am wondering if there was a way to automatically do it? Without all the hard work

A:Flip the rows and columns

Check out the function =Transpose()

Type into the top cell you want to put the new table (I'll use A32), and using your screenshot values:
In cell A32: "=Transpose(A1:K27)" (don't use ") after you type that in, select starting at that cell the same area for your new table - all the cells.

So select A32 and select to AA42, then press F2, hold down Ctrl & Shift, and then Enter, and it will Transpose the vertical to the horizontal and all the corresponding data.

Transpose is brilliant.

I recommend that you then copy the information from that and use paste special and keep values, then delete the complete rows with the transposed array, its annoying cos you can't change the info, so just turn it back into cell data is the best option.

Unless you want to put it on another sheet, and want it to update from your original data.

Read other 3 answers