I have another issue similar to the last one I had regarding converting data exported into Excel into columnar data. Rylo provided a solution for me for my previous problem with this, but now, I've got a similar report with data moved around some and for the life of me, I can't figure out how to adjust his code to make it pull in the data I want.
What I'm trying to do is take the questions under Auto and Home Calls that are located in column 4 and fill in their answers in column 8 on the generated data worksheet. I've attached a sample workbook that has the code I received from Rylo that works perfectly on another book where the questions I'm searching for are in column 1, but again, I can't get it to look at the question in column 4 and give me the answer to that question in column 9. Everything else works with it.
I have a one column spreadsheet. The column contains this data:
1 Name 2 Address 3 City 4 State 5 Zip 6 Telephone 7 Fax 8 URL 9 10 11 Name 12 Address 13 City 14 State 15 Zip 16 Phone 17 URL 18 19 Name 20 Address ... and so on
Where there may be one or two blank rows between the individual records and where there may or may not be a Fax number (or row) in the record.
I am trying to convert this data to a horizontal column format - which works fine if I do a copy/paste special/transpose. However I have to do this for 1,800 records and cannot figure out how to do this reliably.
I gave the above illustration to simplify but, actually this is a two column spreadsheet with individual row labels for every record using the above terminology. In other words the above text is in the first column and the data is in the second. Just thought I'd mention in case there was a way to do some kind of if/then formula.
I have a problem converting hourly data into daily data for electricity import capacity. My file has the following form: | Connection | Date | Period | Available (capacity) | Where 'Connection' identifies the physical location of the capacity (4 possibilities, Germany -> Netherlands; Norway -> Nehterlands; Belgium -> NL; UK -> NL), 'Date' refers to the date, Period refers to hour of the day (e.g. 18:00-19:00) and 'Available' refers to the import capacity of electricity on that conncetion during that hour. I need daily aggregates and tried several things such as DSUM but I cannot manage I've shortened the attached file quite a bit because of the upload size restriction. I am using Excel 2010. I've tried some suggestions in similar topics but none worked for me.
reformat a table in which data for each named person is presented in one row with mutiple columns into a table in which each named person has multiple rows and one column of data. The solution to to this has eluded me so far.
Basically i have month end data ranging from 31/01/2000-31/01/2009 with a value attached to each. I need to convert this data into a daily series with the month end value being the same throughout the whole month. I've been playing around with excel for a couple hours tonight
I am trying to calculate the entire amount of time taken by each person in my team to complete a number of dictation files. The program we use exports the data in this format:
Where it says 5:30, excel shows it in the formula bar as 5:30:00 AM.
What I've been trying to figure out (with very little understanding of how excel works) is how to format that column so that it shows 00:05:30 (as in 5 minutes, 30 seconds) so that I can sum the entire column for each typist and get the total amount of time spent typing per day.
I started changing it manually, but there's rows and rows of the data, for a few months now and its taking me forever. I've been googling my self ragged, but I either don't understand what people say, or I'm not searching the right way (probably a combination of both).
I have a column of data (A1:A38) that I need to convert to a row (A1:AL1) and I can't figure out how to do it. Forgive me if the answer is in the forum but I don't know excel well enough to use the proper search terms. I tried searching on pivot which only yielded pivot tables which I do not *think* apply to this.
WE have many spreadsheets with 1000's of part numbers that need to be converted--- currently the spreadsheets have data in B1 and B2 (part number and description), B3 and B4, and so on--- we need to be able to take B1 and send it to A2, so the part number and the description then end up on the same row--can anybody help this Excel novice convert this data??
I have a workbook where employee scheduling is done on a monthly basis in 15 minute intervals. It is laid out like this:
******** ******************** ************************************************************************>Microsoft Excel - Key Support Services 090813.xlsx___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutB2C2D2E2F2G2H2=ABCDEFGH2Time8/1/20098/2/20098/3/20098/4/20098/5/20098/6/20098/7/200938:00 AM 48:15 AM 58:30 AM 68:45 AM 79:00 AM 89:15 AM 99:30 AM 109:45 AM 1110:00 AM 1210:15 AM 1310:30 AM 1410:45 AM 1511:00 AM Schedule [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
The times go from 8 AM - 8 PM, and all of the cells within the range B3:AF51 are drop downs with the employee names listed. They are conditionally formatted so that we can easily see who is scheduled to work when, and on what days.
The problem that I have is that I like this layout for inputting the data because I can see it all very clearly, but it is not good for archiving or saving past months data. I want to be able to take that view and change the format so that it can easily be summarized in a Pivot Table for all of the historical information.
So, I need to take the date and time and format both of them into a column, put the client name (will be the sheet name) in the next column, and then the name of the staff that worked during that time.
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 12.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD1DateTimeClientStaff28/1/20098:00 AMClient1Employee138/1/20098:15 AMClient1Employee148/1/20098:30 AMClient1Employee158/1/20098:45 AMClient1Employee168/1/20099:00 AMClient1Employee178/1/20099:15 AMClient1Employee188/1/20099:30 AMClient1Employee198/1/20099:45 AMClient1Employee1108/1/200910:00 AMClient1Employee1118/1/200910:15 AMClient1Employee1128/1/200910:30 AMClient1Employee1138/1/200910:45 AMClient1Employee2148/1/200911:00 AMClient1Employee2158/1/200911:15 AMClient1Employee2168/1/200911:30 AMClient1Employee2178/1/200911:45 AMClient1Employee2188/1/200912:00 PMClient1Employee2198/1/200912:15 PMClient1Employee2Sheet1 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
From there I can take a completed month, and quickly run some code to make the change, and add it to the historical tracking where it is all summarized.
The current plan is for me to do this manually over thousands of entries over the next few months, but if I can do it in a quick fashion that would be way better.
< ZEEO:ALL; LOADING PROGRAM VERSION 22.10-0 BSC3i BSC1000 2012-04-10 11:23:47 BASE STATION CONTROLLER DATA NUMBER OF PREFERRED CELLS ........................(NPC).... 3
[Code]....
Now when I am running the macro the output is coming for a single text file as input like..
BSC NPC GMAC GMIC
[Code]....
It should be only a single row as output,but here it is coming in multiple rows(as no of columns) the same data .this is the problem.when I run the same macro for multiple text file it is working properly.
I am trying to figure out an efficient way to convert data into Excel. The data is not in a row/column format. The format that I received the data in is as follows (2 columns; column labels repeating for each name in column A, and the corresponding data in column B. See below. Any ideas on how to best to convert this data into column labels across the top and data in rows under each column heading?
Column A Column B Name John Doe Address 123 Maple Street City Any City State Any State Zip 12345 First Name John Last Name Doe BLANK ROW Name Jane Doe Address 123 Maple Street City Any City State Any State Zip 12345 First Name Jane Last Name Doe
I have linked two workbooks together but now need to be able to convert that linked data to straight text. I have a v-lookup in the report that needs this to be this way. I do not want to use paste special values because that would defeat the purpose of linking the two pages.
I need to transpose or move some of the data on Column B to horizontal position, is there a formula that I can use to do this? Attached is sample of what I want to do.
I'm trying to import data from a rather antiquated software package of race results and present it in a line chart showing each competitor's race result against the date it happened.
The problem is the two ways the data can be exported (shown as option A & B below) is very cumbersome and I can't work out how to create a simple line chart (like the second image) from that raw data. [URL]
What I want to do is turn this data into something like this [URL]
The only way I can think of is to convert/transpose the data into something like below. The problem is I don't know where to begin in trying to do that automatically.
[URL]
I attach the workbook shown in the screenshots which contains sample data for 5 people over 3 races.
I have rxd a pdf file that I need to manipulate I have copied the data into excel and now need it in individual cells:? The data looks like this: 02392950067 19/12/2008 14:33:35 02392950053 Portsmouth 1.9 £0.03. this is:
phone number calling | date called | time called | number called | duration in minutes | cost of call. If you're out there I'd be more than happy to hear from you. the cells to cut the data into will be formatted to take the data in the correct form. 02392950067 19/12/2008 14:33:35 02392950053 Portsmouth 1.9 £0.03 phone number calling | date called | time called | number called | duration in minutes | cost of call.
My problem: I have downloaded national economic data from IMF with several countries for several years. I need several variables and must extract them individually so I a need a clever solution to my problem.
I currently have data that is sorted like below. The "-" indicates a numerical gdp value for the given country for the given year.;
(FYI I could not get the "-" to align beneath 2001-2004 and year respectively.)
Country 2001 2002 2003 2004 x - - - - y - - - - z - - - -
The identifier here is country, and the data is as such structured as cross sectional data, with each yearly account of gdp being a variable. Now, I need this data as panel data, organized as below:
Country year gdp x 2001 - x 2002 - x 2003 - x 2004 -
I sort of say, need to "flip it". As I have 180 countries and 11 years, and need to do this with 2-3 variables, I need either a formula or a VBA to run.
I need to convert data from long to wide form but I haven't been able to do it. I want to try and avoid using vba because I have exactly 5 hours of experience with it, but if there is no other way I will just have to learn it quick.
I need to convert numeric data to proper dates. Example: a cell currently reads 100875 but I need it to display 10/08/1975.
I've already found a VBA script that properly formats new data as you enter it (keying 1298 results in 1/2/1998), and I'm familiar with using =DATE(left,mid,right) to coerce Excel into spitting out a date in a certain format.
The difficulty I'm having is that I need to make existing data display correctly, without adding another column to accommodate reinterpretation of said existing data through a formula. Essentially I'm looking to avoid having to re-key several thousand date entries.
I am trying to convert the data I have to matrix format. I have attached a sample of the data in the file I have attached. The data is in the worksheet "Original" and the matrix format that I am trying to convert is in the "Transformed" worksheet. Can someone please help me with the VBA code for going from "Original" to "Transformed"?
I have data in two columns on the same sheet that I need to transpose into rows on a separate sheet (same workbook). One problem is that I need to reference off one of these columns (column B - in Sheet "Gp Trg Plan") as the number of lines will vary.
Example - Sheet "Gp Trg Plan" Column B Column D Tower Module 1 Tower Module 2 Tower Module 3 Building Module 1 Building Module 8 Street Module 6
Ideally the row will look like.
Example - Sheet "Status WS"
Column A Column B Column C Column D Column E Column F Column G Gp Name Sub Gp Person 1st Mod Date 2nd Mod Date
There is an undefined amount of training modules (columns D in Sheet "Gp Trg Plan"), but no more than 10.
I have a data validation on a cluster of Cells that has a name.range = "match". On clicking on a Cell in Row 61 belonging to range = "match" i get a Data Validation|Custom formula(Alt+A+V+V) "=J61>=0"
now how can i convert this function of Data Validation to a Vba script.
To be Exact what i need is.... If a value is entered in cell belonging to range.name = "match" then check the value in "corresponding row" of the column J. If Value is greater than J then stop vba script and show message box.
If value is not greater than J then run desird Code. I hope the following picture might clear things even more of what im looking for..
I am importing data from my AS400 in Excel, the dates are coming into the spreadsheet as numerics. How do I convert these numbers into a date format? i.e. 3202002 should be 3/20/2002, how do I get the number to that format?
