This is really hard to explain by words. check the 2 files I attached in this post. I checked VIRUS already. NO VIRUS. I have the table like in FROM.xls and I want to change it to a table like in BECOME.xls -automatically- because I have more than 5000 records in my 'real' file.
View 5 RepliesExcell macro to change columns to rows example
From this:
A B C
1 3 5
2 4 6
to this
A 1
B 3
C 5
A 2
B 4
C 6
I'm working on right now is a spreadsheet where we catalog and monitor account information for a handful of groups. I have several different worksheets that contain grouped information such as all groups we have and a list of individual people in those groups. Those groups are in a single column going down and the people are in a single row for that group going from left to right, an example is below:
Master Personnel Listing for Accounts
-------------------------------------------------
Group 1 | Name 1 | Name 2 | Name 3
Group 2 | Name 1 | Name 2
Group 3 | Name 1
Group 4 | Name 1 | Name 2 | Name 3 | Name 4....................
I'm trying to create a macro that will add a blank row each time the values changes in two different columns. I’ve got a macro that sorts the data on two columns, just need to add rows after each change in both columns.
I've found something that'll add rows after each change in one column, the problem is adapting to add rows after each new value in a 2nd column as well.
What I have so far works until it hits the first empty row inserted after sorting by first column.
Sub SplitList()
'
' SplitList Macro
' Macro recorded 05/03/2004 by GaryB
rw = 5
myval = ActiveSheet.Cells(rw, 4).Value
[code].....
Here’s a data sample after performing my sorts, but before the macro to add spaces:
Another, Guy CDP EX 8.36 8.36 0 0 0 0 0
Jones, Some CDP SS 3.3 3.3 0 0 0 0 0
Justa, Nother ESP NV 22.45 22.45 0 0 0 0 0
Person, One ESP SS 23.36 23.36 0 0 0 0 0
Tom, Rich SSP MA 2.35 2.35 0 0 0 0 0
Dude, Steve SSP UN 63.86 25.36 5 1 2 1 1
Blanke, Spaece SSR MA 12.24 12.24 0 0 0 0 0
Try, Again SSR MA 15.25 15.25 0 0 0 0 0
And after the attempt to add rows: (note that it added a blank row between the first and 2nd rows (which differed in the 3rd column), but that it then stopped.
Another, Guy CDP EX 8.36 8.36 0 0 0 0 0
Jones, Some CDP SS 3.3 3.3 0 0 0 0 0
Justa, Nother ESP NV 22.45 22.45 0 0 0 0 0
Person, One ESP SS 23.36 23.36 0 0 0 0 0
[code].....
And, what I’d like it to look like:
Another, Guy CDP EX 8.36 8.36 0 0 0 0 0
Jones, Some CDP SS 3.3 3.3 0 0 0 0 0
Justa, Nother ESP NV 22.45 22.45 0 0 0 0 0
Person, One ESP SS 23.36 23.36 0 0 0 0 0
[code].....
You can see, it seems to be hanging up once it hits a blank row after first round of inserted rows.
I have a large spreadsheet that contains and employee number and then up to 5 entries of Dependants (each has its own column). What I need to do is have 1 for each of the dependant information instead of 5 and have multiple rows for the employee if they have more than one dependant.
At the moment I am sorting the data by dep 1 name, pasting the data into a new sheet, deleting all the dep 1 stuff, sorting by dep name 2 and pasting that.........etc.
It doesn’t take too long to do but I just wondered if there might be a quicker way as this will be something I will need to do more often.
I am trying to write a macro that will take data from column A and use it as a column header in column C, and then take the corresponding data in column B and list it under the column header in column C. When the value in column A changes it will move on to column D and then list the corresponding data from column B underneath it in column D until all of the data in the 2 columns is spread across the worksheet. Example:..............
View 2 Replies View Relatedi need to mark some row (which has some content written in), mark other row(with data too) and switch/transpose them mutual. when i was trying transpose method, which is using for switching rowns and columns, it wrote me error, that data are overlapping. it means it cant work on same things (rows > rows, columns > columns).
View 14 Replies View RelatedThis is a piece of a spreadsheet meant to track patient Range of Motion performance over a max of 4 weeks. How do I calculate % Improvement [write the formula for column J] to account for the fact that sometimes a patient is discharged after being seen 2 weeks OR 3 weeks or 4 weeks. I don't want to change the formula every time.
The SUMMARY % Improvement in J7 - will be changed to the average of each individual patient % Improvement from column J.
Looks like I need a dynamic range from what I can see from Googling. Do I need to use Offset ?
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
I have this in Column A, with about 120 entries:
Company 1
Contact 1
Address 1
City, ST, ZIP 1
Phone 1
Fax 1
Company 2
Contact 2
Address 2
City, ST, ZIP 2
Phone 2
Fax 2
Company 3
Contact 3
Address 3
City, ST, ZIP 3
Phone 3
Fax 3
I want this:
Company 1 Address 1 City, ST, ZIP 1 Phone 1 Fax 1
Company 2 Address 2 City, ST, ZIP 2 Phone 2 Fax 2
Company 3 Address 3 City, ST, ZIP 3 Phone 3 Fax 3
all the way down.
I can't figure out how to record the macro to tell it to then skip the blank line, collect the next set of data, and put it in the next row. I can do it for two, but then it just replaces the first two with the next two and I lose data.
Column A is numbered 1 -100 successively Column B thru D contains data that goes with the assigned number in column A. I need to be able to move rows of data in column B through D to a different set of rows all at the same time (not one cell at a time) without disturbing the set numbers in column A. And with that, have all the other rows of data automatically adjust accordingly(not to be deleted or replaced).
View 1 Replies View RelatedI have individual data files (one for each participant). Each file has 64 rows (one for each trial in an experiment) and 18 columns (one for each variable). I would like to create a master data file in which each participant is represented in a single row. I will then have 1152 columns (18 columns for the variables, repeating 64 times so that each trial is represented).
In other words, my individual data files look something like this:
Participant 1 (title of data file)
Trial/var1/var2/var3
1 55 43 65
2 54 43 56
3 33 23 56
and I want to compile all the data into a single file that looks like this:
Participant/Trial1_var1/Trial1_var2/Trial1_var3/Trial2_var1/Trial2_var2/ ...
1 55 43 65 54 43
2 . . .
I know how to record macros and use IF, THEN, AND, & SUMIF statements creatively, but that's about it. I was hoping to record a macro that I could use on each individual data file to turn it into a single row, and then paste each row into the master file.
How to align two columns that sometimes have rows that are unequal in number. The data is sourced from a web page that has a table layout and the table lays out the one to many values this way in the table. My data looks like this:
Table ...Term
t1 ..... term1
t2 ......term2
..........term2.a
..........term2.b
..........term2.c
t3 ......term3
..........term3.b
The editor removes leading spaces. In the column Table there are only three rows but in column Term there are multiple rows per single row from column Table. So one row t2 for column Table has three rows in column Term. Are these cells merged and can I in a mcro detect if the cells are merged? How is the addressing of the merged cells in a VBA macro?
I have a formula that I would like the column references to change as I drag the formula down. I would also like the row reference to stay the same.
I have attached my workbook for reference. I listed the value in red I would like to achieve
Book1.xlsx
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB:
Public Sub DelRows2()
Dim Cel As Range, searchStr, FirstCell As String
Dim searchRange As Range, DeleteRange As Range
[Code].....
I want to change the back ground colour of each row until G column when there is a text in the first cell. I have attached the excel that I want the background colour to be. Like suppose, F1 is the details of family 1, F2 is for family 2. I want each family to be coloured. The rows are not fixed for each family. If F1 details are coloured green, Fr is orange, F3 is green and F4 is orange again. And the number F1, F2, F3, F4 and so on are not ordered in ascending. They can be random. But I want the colour coding to be the same as what I have shown in the excel. I have so many rows like that. How do I do that using excel. I have tried conditional formatting. But it doesn't work.
View 2 Replies View RelatedI have a csv file which I opened in excel file then i converted the csv to excel columns format using the text to columns delimited.
Now I need to change it back to its original form which is the csv.
I have the following code to check and see if the user enters a number into a specific column. If so, and if the number is too high, the routine will automatically lower it to an acceptable number. However, I am wondering, is there not a simpler way to do this, besides using a bunch of OR statements?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Or Target.Column = 4 Or Target.Column = 6 Or Target.Column = 8 Or Target.Column = 10 Or Target.Column = 12 Then
Application.EnableEvents = False
If IsNumeric(Target.Value) = True And Target.Value >= 105.6 Then
Target.Value = 105.4
End If
Application.EnableEvents = True
End If
End Sub
This works just fine, but it seems kind of sloppy to me.
I was wondering if there might be a way to put the column numbers into an array, and then simply check if the Target column equals any number in the array. But I do not know how to do that without looping.
I have a macro that changes the color on a row, but depending on which cell I am in different columns arwe changing color. What I want is that if I make a change in row 1 want the color in A1:L1 to change and if i change row 2 i want A2:L2 to change, so alwaws the columns A to L in respective row. My macro
Sub Markera_Ny()
Dim intRadnr As Integer
Dim intStartrad As Integer
Dim intSlutrad As Integer
Dim Box As Integer
Dim Rad As Integer
intRadnr = ActiveCell.Row
intStartrad = ActiveSheet.Range("First").Row
intSlutrad = ActiveSheet.Range("Last").Row
If intRadnr < intStartrad Or intRadnr >= intSlutrad Then
MsgBox "Macro can only be executed in rows 21 to 1000"
Exit Sub
End If....................
have a large dataset where I want to move some data from rows into columns and other data from columns into rows. I have enclosed an example. It is indeed only an example as the original has many more rows and colunms. What I want to do is to move the the years (columns) into rows and move Product (row) into columns. The example shows how it currently looks and how I want it to look.
View 2 Replies View RelatedReally need a formula for a traffic light system
ie
if 3 colums have entries no
4 Maybee
5 yes
and it highlight it in green amber or yellow
For example, on column X, if I type 20, bob or bobafett it type 2.0 instead but on column I if I type bob, it will type 3.0
they would all be edited manually
or even better, no matter where I type it on excel, it would go in the right column, like bob X, then it would change to 2.0 and go directly in the X column
AS you can see in the attached file, I have several collumns of data going form 2012 to 1992. I would like to know the quickest way to invert this so the data is presented 1992 to 2012.
View 5 Replies View RelatedWe are currently working on uploading product (XML) to our website, we have some old product data done and now there are some new product sheet came out and we have 30 of them. Between the new and old product sheet, we have to add new products, remove discontinue products and update all the old product's pricing.
First come to first, we need to find out which are the old products and which are the new products. Yes, the manufacture didn't separate the new from the old. I have put together a sample Excel, please see attachment.
I need to compare them and highlight the cells in in GREEN on both columns when they are match.
Column A (Old SKU)
Column B (New SKU)
Column C (New COST)
Column D (New MSRP)
Column E (New SPECIAL PRICE)
Update new pricing from NEW Columns C,D,E to OLD Columns F,G,H when the cells from Column A and B are highlighted
Column F (Old COST)
Column G (Old MSRP)
Column H (Old SPECIAL PRICE)
Can that be done within Excel build in functions?
So I've got a drop down list in cell B73 That when I change the selection I want it to copy the cells below it (B74:B94) from one of the charts above it. Through the power of Google I found this: http://www.eggheadcafe.com/conversat...eadid=29484871, someone who had the same need as me, and edited it accordingly to my needs. It however, is not working. I started with Case 1-7 and changed them thinking they need to be the same as the list but that hasn't fixed it either. File is attached, and below quote is what the VBA coding currently says.
View 9 Replies View RelatedI have a big table with lots of columns for which I want a pivot to sum the data in the columns. Most likely because there are lot of blanks in the data Excel automatic makes a count of the data when I drag it into the pivot table. Now I have to change for each column one by one the Value field settings from count to sum. Is there a way to change the Value field settings for multiple columns at once from count to sum? If not can I somehow change the settings of the pivot table so default the Value field settings will be sum when I drag it into the pivot table?
View 4 Replies View Relatedmacro for an Excel 2007 file. It has to be a macro. I have cells in two columns I need to look at. Column A will have a value of cat, dog, or mouse. Column B will have a date. If A2="mouse" AND B2<"1/1/2013" Then A2="" ElseIf A2=mouse" AND B2>="1/1/2013" Then A2="mouse". I don't need anything to happen if A2 is equal to cat or dog. The macro has to move down the entire A and B column: A3/B3, A4/B4, and so on until the end of the columns. Hope this makes sense. I've tried a few macro samples I found online but they don't quite fit what I need done, or I don't know how to modify it.
View 2 Replies View RelatedHere is an example list:
Networks
Hardware
Networks
Software
Networks
Resources
Apps
Hardware
Apps
Software
Apps
Resources
Domain
Hardware
Domain
Software
Domain
Resources
Print
Hardware
Print
Print
All I'm trying to do in the second column is something that can say if there is a change in column A, then restart the order, or not..
I have just been sent a strange file, which I have attached, on its only sheet columns "A" and "B" have data entered in them, but they are not visible. You can access them if you select column "C" and use the left arrow key, but all attempts at unhiding and changing the column width are fuitless.
I have tried selecting the ENTIRE sheet, then Home>Format>Unhide Columns but it does nothing I try selecting the entire sheet, then Home>Format>Column Width and setting it to something, like "20", but again it is fruitless.
I even tried using a macro in the "Immediate Window" to ask Excel whether the columns are hidden (by using "?Columns(1).Hidden" and it returned "False") then I used a similar macro ("Cells.EntireColumn.ColumnWidth = 12.75") and again - nothing! :O
I need for it do perform the same action on multiple rows/ cells of data. What adjustment to the code needs to be performed to do so?
Sub fconcat()
Range("C1").Value = Range("A1").Value & Range("B1").Value
For i = 1 To Range("A1").Characters.Count
Range("C1").Characters(i, 1).Font.Name = Range("A1").Characters(i, 1).Font.Name
Next
For i = 1 To Range("B1").Characters.Count
Range("C1").Characters(Range("A1").Characters.Count + i, 1).Font.Name = Range("B1").Characters(i, 1).Font.Name
Next
End Sub