Macro To Move Data To Another Format
Feb 25, 2014
I'm looking for a macro to select data from a larger data set, and then put it in another worksheet. Basically, I want to select any cell in column A that contains "SO5" and move those to the column A of a separate worksheet. Then, for each cell that contains "SO5" would like to move to the right 5 columns and down 3 rows and select from that cell all the way to the end of the table. There may or may not be data in all of the table cells, so the selection should be based off of the column headers which will always have values. I would like to take that data and put it with the corresponding "SO5" in the new sheet.
Here's a sample:
MacroSample.xlsm
This is a simplified version, but I'm trying to get the data from sheet1 into the format in sheet2. Keep in mind that the number of columns and rows may change so it needs to be as dynamic as possible.
View 1 Replies
ADVERTISEMENT
Jul 30, 2014
I've got a workbook with a sheet named Data that has the information laid out in a single row. The data can't be fit onto a single sheet for printing / viewing so I've got a second sheet named Presentation that the data get's laid out to 2 rows so that it can be viewed / printed on a single page.
I'm handing this off to non-technical people so I need an easy way to populate the presentation sheet with the data. I've layed the data out using =Data!A1 however, the drag auto-fill doesn't properly fill it down since it's single row to double row.
Example of Data Sheet
Patient
Chart
[Code]....
View 3 Replies
View Related
Mar 28, 2014
I have two worksheets. Sheet 1 has 2 columns, Column A the restaurant's name and Column B contains the review score. So sheet 1 is kinda like this:
Restaurant |Score
Ruby Tuesdays 80
TGIF 78
Outback 92
Sheet 2, Row 1 column B-E contain restraurant names (only on the top row, like field names).. i.e. I manually put the date in because typically the projected date is different from the actual review date.
-A----------- B ----------------C ------D-------- E-----
Date |Ruby Tuesdays|Olive Garden|TGIF|Ruths Chris|
I need the data from Sheet 1 Column B moved to sheet 2 in the next open row (i currently have data in row 1..the field names and down to row 35). This will be continuous so each time i need it to add the score as a new row in the correct field (restaurant name), IF the restaurant isnt listed, I want a new field named with the restaurant name and then place the score in the correct row and column. So, in the example I'd need Outback added.
View 9 Replies
View Related
May 12, 2013
I am working on a project and would like to see if there is a solution for it. i have a workbook that has data entry that is summarized at a master level but I need to automatically use a formula to update another sheet after clicking an udpate button. The data from the data entry sheet needs to be allocated to all the lines that has the same master item based on the formula. A test workbook is attached ...
View 1 Replies
View Related
May 12, 2014
I have an issue with groups of data in a row, that I need to copy into a new row beneath the original. I need a formula or VBA that will perform this function:
If any data is present in columns AG-AK, then copy that data into a new row below, into cells AB-AF. Also copy column B (this data is always present, it is the name of the project data contained in the original row)
If any data is present in columns AL-AP, then copy that data into another new row below, into cells AB-AF. Also copy column B (this data is always present, it is the name of the project data contained in the original row)
View 3 Replies
View Related
Sep 3, 2007
Im trying to do a macro that will move data from a number of sheets based on a flag in column W on each sheet.
Basically if the flag in column W is "YES" then i want it to copy the whole row and append it (if thats possible?) into the sheet 'Journal'
I have attached a cut down version with only 3 sheets (one of which is the 'Journal' sheet) and only a few rows on each sheet.
View 10 Replies
View Related
Feb 21, 2010
I have a single workbook with five spreadsheets. My goal is to move data along a path (or work flow) from one sheet to the next by using a "trigger" pulldown menu choice.
Sheet1 = Prospects
Sheet2 = New Sale
Sheet3 = Upgrade Sale
Sheet4 = Won
Sheet5 = Lost
Sheets 1, 2, and 3 use the same data layout for column A - K.
Sheets 4 and 5 have the same A - K as above and also have columns L - R in common.
The last column in sheet 1, 2 and 3 is a pulldown menu (New, Upgrade, Won, Lost).
Data rows on each sheet start at row 7.
The goal is to use the pulldown choice to remove the data from the current sheet (ex: Prospect) and add it to the next open row in the required sheet (ex: New or Upgrade). I also need to be able to make a similar move from New/Upgrade to Won or Lost.
View 11 Replies
View Related
Apr 24, 2012
I have data in a single Excel worksheet, single tab, columns A:C.
I want to move that data into Word.
The format in Word:
Contents of A1
Contents of B1
Contents of C1
Blank Line
Contents of A2
Contents of B2
Contents of C2
Blank Line
etc, etc, etc, etc....for all rows in Excel.
View 9 Replies
View Related
Mar 26, 2013
writing a Macro which will move data from a cell to an adjacent cell.
E.g Column B has only data in certain cells, I would like the Macro to work down the column and every time it encounters data in Column B to move it into the adjacent cell into Column A.
View 4 Replies
View Related
Oct 29, 2013
My data is now 500+ lines long but the button is on row 1. I have to get back to the top to click it. Can I auto move the button down with my data. Say to one line above my data. i.e. 499 and move as my data increases.
View 3 Replies
View Related
Jun 5, 2009
I have sheet 2 with a table with data in A2 thru X500, A1 thru X1 are my headers, A1 data is either Forecast or Sold, B1 data is Month, C1 is Value. I first need to Filter the data by Forecast, then by a specific month then by the value in decending order (keeping in line the data thru x which are headers I don't need to sort by). Next I need to take the 1st 10 lines by a specific month; A thru X and copy it onto a defined section on Sheet 1.
I then need to do this for the 2 more times for the 2 proceeding months. So If we start with Jan, I need the macro to do Feb and march as well.
The end result is that I have Sheet 1 with 3 sections on it each section is 10 lines and the resulting data is my top 10 forecasted deals for Jan, Feb, March. The macro must be able to do this for each month, so if June is chosen the 3 months are june, july and Aug.
View 9 Replies
View Related
Jul 22, 2009
Is it possible to have a macro compare two wokrsheets and move the totals from one sheet to the other worksheet.
Here are the worksheets: ....
View 9 Replies
View Related
Mar 25, 2014
I have a worksheet that has time clock information. I also have a resource on the web to see some information and even export it. In the exported data it looks as follows:
A1 contains an individuals name
B1 says times i.e. "8am to 5pm"
I would like a macro that takes the data from A1 and moves it to another worksheet in the B4 position down, and take the times from B1 and move the first time to the C4 position and the last time to H4.
View 9 Replies
View Related
Apr 13, 2013
This is the macro I am using
Sub Macro3()
'
' Macro3 Macro
'
Range("C2").Select
Selection.Copy
Sheets("Table").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
[Code] .....
It does everything I want but take F2 info and send it over. But F3-F6 does go over.
View 6 Replies
View Related
Nov 5, 2009
I have had several intro programming classes, but not with visual basic. I've noted the problem lines with comments below. The problems are probably very easy syntax error.
Sub list()
Dim counter As Integer
Dim counter2 As Integer
Dim id As Double
Dim id2 As Double
Dim palletnum As Integer
ActiveSheet. Name = "Pallet Detail"
Sheets.Add
ActiveSheet.Name = "Pallet Summary"
Sheets("Pallet Detail").Select
View 7 Replies
View Related
Dec 13, 2011
I am using Excel 2010 and need a macro that can convert data from rows to columns. I have read several posts about this subject but have no experience with macros and don't know how to change the macros to fit my scenario.
Here is what I currently have:
Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008........FL
11111......2000........FL
12121......1999........GA
33222......2000........AL
33222......2011........AL
33222......2001........MS
Here is what I need it to look like:
Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008.......FL.......2000........FL
12121......1999.......GA
33222......2000.......AL.......2011........AL......2001.......MS
There are up to 4 vehicles/locations per account number, and I need 1 account number per row (the dots above are for spacing only and not part of the actual data).
I could do this manually but because I have so many rows of data it could take days or weeks. Is there a macro out there that can do this??
View 3 Replies
View Related
Mar 4, 2008
I am trying to create a macro that will look in Column B for the word "RPLCASE." Whenever "RPLCASE" appears in Column B, the cells in that particular row will move over (Right) one space. I am simply trying to align the data from a file that I FTP'd from Net Term to Excel. You can see how the data should look in the 2nd example.
14-Feb-08JERRPLCASE671150536:116:56RPLCASE78308147:077:54RPLCASE89431228:228:57RPLCASE910592289:159:59RPLCASE10114732310:0010:48RPLCASE1112146811:4511:58RPLCASE12136563612:0612:56RPLCASE13144122113:0313:58RPLCASE14154642314:0114:23**********--------------TOTALS4632228LDLPKGPIK671361126:046:58PKGPIK781461247:007:56PKGPIK8963598:168:55PKGPIK9101591309:019:59PKGPIK101115113910:0010:54PKGPIK1112968711:3211:59PKGPIK121319916012:0012:59PKGPIK131412310313:0013:59PKGPIK141518716414:0014:59PKGPIK15161119715:0015:54PKGPIK16173316:3016:31**********--------------TOTALS13741178
View 9 Replies
View Related
Jul 23, 2008
I need to create a macro to move variable multiple horizontal data to vertical format with certain infomation on horizontal will be duplicated following that variables. It's looks like below where you can see variables data in column F, G, H and I are moved vertically and at the same time column A, B, C, D and E will be duplicated following the variables allocation. I've tried to use transpose but it too manual and now looking suitable macro to help on this function
Original DataAccountDim 3Dim 4AmountCurrencyV20228V20242V20211V202044006003300BXXX 9.4USD0.591.923.343.554006003400BXXX 88.17USD5.5118.0331.3233.314006003500BXXX 7.27USD0.451.492.582.75Process to automateAccountDim 2Dim 3Dim 4AmountCurrency400600V202283300BXXX 0.59USD400600V202283300BXXX 1.92USD400600V202283300BXXX 3.34USD400600V202423300BXXX 3.55USD400600V202423400BXXX 5.51USD400600V202423400BXXX 18.03USD400600V202113400BXXX 31.32USD400600V202113400BXXX 33.31USD400600V202113500BXXX 0.45USD400600V202043500BXXX 1.49USD400600V202043500BXXX 2.58USD400600V202043500BXXX 2.75USD
View 9 Replies
View Related
Apr 16, 2013
I want to move to the next row but i don't want to make another format just when i begin to write in the next row the format appear automatically like previous row ?
Note : format include formulas ,borders and colors every thing in previous row ...
View 10 Replies
View Related
Jul 16, 2014
so to start off im not able to attach things due to security reasons, what i need is either 1 of 2 macros. if its possible, both are related. one possible is as follows: i need it to go through a certain column (say I for example) and look at the information in it, this information can vary from peoples names along with dates and other stuff, i want it to look through these and if anything has a date today and prior i need the cell to be highlighted. problem is sometimes it might have 2 dates, or no dates. it should have todays date, their name, other information, and future date of something. but not everyone does, this is the macro i dont think that can exists.
2nd macro possibility the other macro uses the first sheet, AFTER been highlighted, normally by hand, and takes it to another workbook and puts in in certain spots. so the first sheet has names of everyone in column K. what i need is it to look at column I and if its highlighted take entire row to other book, and put into sheet under the person name in their tab. the second book has a tab for each person (at this time 18 tabs) which can fluxuate, and each tab is the persons first and last name, without spaces. since when i put sums on main page it didnt want to work with the spaces i had to omit them. again im not sure this is possible.
View 6 Replies
View Related
Aug 24, 2009
I have been trying to produce a macro that will format data of varying lengths. Basically, I pull data from a database and export it into excel. I have a macro that will format the first few lines fine. Unfortunately, there are a few lines of info at the end of each report that need to be deleted. I cannot figure out how to make excel reach the end of my data a delete these two lines. If I build the macro on one report that has 100 lines and delete line 99 and 100, and then attempt to run the macro on a report that has 150 lines - lines 99 and 100 are still deleted.
Is there a way to delete the last couple of lines of data of a spreadsheet?
View 9 Replies
View Related
Jun 28, 2012
check and format some data with some code.
I have 100,000+ rows that look like this
Sheet1 *ABCDEFG1start_date start_time end_date end_timeTypeSOC Before SOC
After208/01/201218:36:2008/01/201218:40:00T**308/01/201218:45:1208/01/201218:50:44T**408/01/201218:52:1208/01/201219:50:46C3446508/01/201219:54:2708/01/201221:44:40C4681608/01/201221:48:4108/01/201222:02:36C8187708/01/201222:06:4608/01/201222:11:49C8787808/01/201222:15:5008/01/201223:02:00C87100909/01/201208:16:5109/01/201208:44:08T**1009/01/201208:45:1109/01/201208:49:55T**
I'm trying to merge all the "C" types in Column E into one row like this
Sheet1 *ABCDEFG14start_date start_time end_date end_timeTypeSOC Before SOC After1508/01/201218:36:2008/01/201218:40:00T**1608/01/201218:45:1208/01/201218:50:44T**1708/01/201218:52:1208/01/201223:02:00C341001809/01/201208:16:5109/01/201208:44:08T**1909/01/201208:45:1109/01/201208:49:55T**
So basically, I need to use the 'start date' and 'start time' from the first row and the 'start date' and 'start time' from the last row. Also use the first 'SOC before' and the last 'SOC' After'.
I have more data in column h - column z, so I need to delete the entire row of the additional "C" rows. Otherwise the data will be misaligned.
View 2 Replies
View Related
Jul 22, 2012
Macro or formula to reformat some data.
I have like data like this:
Column A = Date
Column B = Single Distance
Column C = Total Distance for that date
Sheet1 Â ABC1DateIndividual Distance Total distance 204/01/2011919304/01/20111019405/01/2011
212505/01/20111012606/01/2011926706/01/2011326806/01/20111026906/01/20112261006/01/2011226
It would be really useful if I could format it like this
Sheet1 Â FGHIJ1Total Individual 219910Â Â 312210Â Â 42631022
So basically, it puts all the information on one row for a particular date.
View 7 Replies
View Related
Dec 15, 2006
I have cells which contain dates in the typical Excel format. IE: Jan 3rd, 2000 in unformatted form shows up as 36528 in Excel in Cell A2. What I would like to do, in VB, is take the contents of that cell A2 (36528), and turn it into a date in the form of:
Year,Month,Day
(For instance, Jan 5th 2001 would be
2001,1,5
And then assign it to a variable
View 4 Replies
View Related
Feb 23, 2009
I import a raw data file from our phone system into sheet 'imported', I then want to display the data on sheet 'formatted' how I have displayed it for the purposes of this example. I had a solution to this but the formatting out of the phone system has changed.So I need it to list the agents names, and then the corresponding time for each activity. The activities are listed on B3:I3.
View 2 Replies
View Related
Sep 16, 2009
Iam just trying to format an raw data(which has morethan 40,000 rows) which is converted from notepad to excel.
The major problem the token number which is below the name and quantity i need to bring the number to left side of the row.
i think this has not given an clear picture so iam attaching an sample sheet
View 6 Replies
View Related
Nov 7, 2009
I have a worksheet (“A”) that is populated from a second sheet (“B”). The data on “A” changes as necessary using another Macro. Most of the data on sheet “B” is numeric and comes from various formulas and results in numbers with too may digits after the decimal point. I can format sheet “A” to turn 58.22222222 into 58.2 but when I run a Macro to clear the data I lose the formatting. I need to find a way to clear only the data and not the format, or find a way to add a mask on specific columns in the macro that copies and pastes. Here is the Macro to clear the data.
View 3 Replies
View Related
Feb 20, 2007
I am having a problem with a macro which has only started occuring on Excel 2000. It worked fine on Excel 1997. Basically, the Macro converts a text file (with comma delimiter) to an excel format. The problem I am having is with some of the date fields in the text file.
All dates in the Text file are in the format dd/mm/yyyy (i.e. 01/02/1990)
If I convert the Text file to Excel format manually through the wizard it correctly converts the date in Excel to dd/mm/yyyy (01/02/1990). However, If I run the Macro I have created, it converts the date to mm/dd/yyyy (02/01/1990).
This only seems to happen if the dd field is less than 12.
I attach a sample of the text file and Excel file (with incorrect dates) and also the Macro.
Basically I need the Macro to convert the dates in dd/mm/yyyy always....
View 9 Replies
View Related
Jun 14, 2013
I export raw data from my accounting system each month that I then format for use in another application that uses the data to produce customer statements. I have attached a file that shows the raw data in the first sheet and the formatted data in the second sheet. I recorded a macro while I did the process but I need to change the code to deal with dynamic data as the number of rows may be different every month.
Here are the steps I go through:
1. Clear the first 3 rows and the last 5 rows
2. Copy the totals in the last row of the data and paste them in the first row
3. Subtotal the different categories in row 2 and add a validity check
4. Add a new column A with a formula to add customer numbers on each line
=IF(ISERROR(FIND("00000",B5)),A4,B5
5. Copy the formulas and paste values over them
6. Filter the data to show blanks under the "Doc Date" column, delete all rows
7. Filter the data to show blanks under the "Type" column, delete all rows
8. Filter the data to show "Total:" under the "Apply No" column, delete all rows
9. Turn off filters, format all numbers in accounting format
10. Check the validation at the top to ensure no transactions were deleted
View 6 Replies
View Related
Aug 17, 2008
We have a system that normally dumps our data in the format of column K, with all of the data in one column and the same spacing you see below in red. The problem is when the system is slow, we have to manually dump it, and when we manually dump it, it comes out spread out from column A to column I. For the macro to work without bugging out on me, it has to be in the same format as column K, with exactly the same spacing.
I have tried text to columns while importing, amongst other things, but have not had any luck. So in a nutshell I need to be able to make column A thru I, look just like column K with exactly the same spacing. The data dumps are different every day.
I have a strange feeling I will not get a reply on this question, because it is so strange, or that I failed to articulate it correctly....
View 9 Replies
View Related