I often download data from various web sites - this is data that is displayed on a web page in tabular format, and often consists of hundreds of lines. I typcally Select All, then Copy, then go to a blank spreadsheet and Paste. After a bit of reformatting, I'm usually in good shape. The problem is, that sometimes the contents of a cell might have been "5-2" (without the quotes), and it comes through on Excel as "May 2". Without proofing each cell, I can't just make the assumption that the original was "5-2" - it might have been "5-2-10" (but not a date).
The normal Paste Special options are not available when I do this, so I can't paste them in as values. I have tried formatting the worksheet at text prior to doing the copy but that does not work.
I am trying to use vlookup function with web query data and some of the data has embeded characters I can not see and vlookup does not work (can not match even though numbers appear to be the same). I have attached a test file with the data and formula and a detailed explanation.
I have a problem with text to columns with data downloaded from our website. The address field is coming through with the whole address in a single cell with multiple rows. I have tried the following but all I get is the first row in the first colimn and the rest of the data:
Text to Columns, Delimited, Other and ALT-0010 Text to Columns, Delimited, Other and ALT-010 Text to Columns, Delimited, Other and ctrl-j
I have even created a macro to replace the hard returns with commas and then use comma as a delimiter but that still does the same.
I have attached a file with two of the records (1 tab for the original data and one with the commas)
Finally, I have tried this in both 2003 & 2010 and get the same issue in both.
The government was just kind enough to send me some data I asked for in the form of a spreadsheet. Came in a Yahoo email. Document was blahblah.xls
I downloaded it.
Clicked and Excel opened it without any error messages.
What I see is one white bar in the spreadsheet section. It has three sections where you would have three columns.
On the left it says F279, which I take to be the cell address.
Then to the right of that there's a sort of elongated blue button with 'fx' written on it and a dot at the left end that I can use to grasp it and slide it left and right.
To the right of that it says 6169, which is the content of some cell.
Hover over a couple of these things and it says 'name box' and 'formula bar' at different times (sometimes I can't get it to do it.)
There's data there. I can't do it now but somehow the first time I opened it I was able to scroll one or the other column (i.e. one at a time only) up and down and see there was content there.
Is this thing fixable or should I get back to them and ask for a rerun? I would delete this is I could but it doesn't seem to offer that choice.
Did a 'print preview' and after that it came up in normal view. Well, nearly, it was down at the bottom of the screen but I was able to drag it up and now I've got it.
Report 1 is downloaded from SAP into Excel and Report 2 is downloaded from AS400 into Excel.
Report1 contain 3 columns as follows: Column A - Invoice # Column B - Invoice date Column C - Invoice Amt
Report2 contain 5 columns as follows: Column A - Invoice # Column B - Ship Date Column C - Check # Column D - Amt Paid Column E - Payment date
I tried to use the Vlookup function in Report 1 Column D to lookup the matching Invoice # in report 2 and bring in the corresponding Amt paid. It did not work and I assume that this is because of the formats in the downloaded files. I did try to change formats for the columns involved but this did not work. This function works fine if both files were setup in Excel, however in these downloaded files even though saved as excel workbooks, something prevents this function from working. I do know for a fact that the same invoice number does show up in both these files but the vlookup does not pull up the required Amt paid from Report 2.
I need to format some climatic data from a weather station into a desired format. There are a lot of cut&paste and transposing involved. I think it will be easy but tedious for anyone trained in VB to write a macro but unfortunately I am not. I've tried recording a macro but it turns out that it is not general enough to deal with all the spreadsheets that I've got.
I've attached a spreadsheet which shows the original format (in sheet 1) and the desired format (in sheet 2). To briefly describe, I only need the temperature data for 8am and 2pm, the rest of the information in sheet 1 is useless to me.
See attached file. A colleague is downloading rows of data from a website which contains a number field Excel is currently treating as Text after being pasted in. My spreadsheet includes just a sample of the many rows of data however as you can see the VALUE function refuses to convert these text values to numbers. How these might be converted and why the VALUE function refuses to work in this case?
vba in excel 2013 pivot table that updates from an online CRM rows are a to h and it can be any number of rows. What I am trying to do is track progress. In column f values indicate probability for success 10 25 45 90, which can go up or down. The pivot table is refreshed to get the latest values from CRM. the update is handled by a connection to the crm not in the vba.
So far I been researching methods to conditionally format values that went up, down or remained the same since the last refresh with up down and across arrows. I have managed to piece together what I think should work but alas it is not. So I have come to you internet.
My code first clears any formatting and goes down the column avoiding null or empty cells, comparing the values in column f to values in column j. I have 3 conditions greater than, less than or equal to, and would like add an icon for each based on the result of the comparison.
Finally when it finishes the column the code copies the current values in the pivot table column f to column j outside the pivot table which i hope to be able to hide once the cf works. The code is below
I have a sheet with data 7 columns wide and up to 1500 rows long. Below are a few lines from the sheet. The data from the sheet is grouped into "months" as shown in the header down the first column, "Show June, 2013..." and "Show July, 2013...", and so on. Is there a way I can make Excel re-align the data into ONE long row of data for each month? Each new month row must be "triggered" by the text (like the word "Show") because the actual cell number of where the new month begins may change with new updates. I want to do this because I plan to refresh the data once at the end of each day from the web and apply the new data to the "next" row of data. So eventually, I can have a history of "Last" values I can chart for each "strike" for each month.
I am seeking the expertise of you knowledge folks to advise me whether it is possible to create a "script" in Excel to jumble up certain letters in an entry?
Let me explain. What I have are 4 separate columns with data in them. I need to somehow rearrange them into a certain format, like an encryption I suppose.
Here's an example of some data I have :
COL 1 COL 2 COL 3 COL 4 ABCD EFGHIJ KLM NOPQ
What I need to do is jumble those up so they appear in the following format:
COL 1 COL 2 COL 3 COL 4 CDAB GFEHIJ LKM NPOQ
Is it possible to do this in Excel? You're probably wondering why I don't do it manually, but there are over 20,000 records that need jumbling up and obviously that would take a very long time.
I think I have a similar problem as this thread http://www.excelforum.com/excel-prog...o-columns.html. The answer is probably somewhere in the forum, but I looked around and I can't figure it out on my own.
Basically, I copied some data off a website and now I am trying to organize it so it is usable. Here is my example. I would like to put the data below into usable columns. The only data I need is the hospital name (column A), location (column B), Births (column C), and Beds (column D). This is how the data currently appears in excel:..............
See the attached file. In the RawData tab, I have the Type, The Date, and total for that date and type. Using a macro, i would like to redesign the output(onto another sheet) so it can be updated to a database table(i already have the code to export to database). The output should look like what is in the "OutputExample" tab. Figures in the OutputExample tab are only for the first three dates. So currently, I have the output as:
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?
I have a data set in excel which is the following format which I would like to convert into a text file with a different format. The excel sheet format is as follows:
Check and format some data. I have 100,000+ rows so kinda need a macro to do it.
I have data like this
Sheet1 ABCDEFG1start_date start_time end_date end_timeTypeSOC Before SOC After 208/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:40C
[Code] .....
I'm trying to merge all the "C" rows in Column F into one row, like this:
Sheet1 ABCDEFG14start_date start_time end_date end_timeTypeSOC Before SOC After 1508/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 you would just keep the first row,but replace the 'end date' and 'end time' with the 'end date' and 'end time' from the last row and same with the SOC.
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.
I have data in excel sheet named LSR from A2:L3000 sample as below. i want to have a VBA code to change the data as below.
Data before changing
FK PLATE NO Vehicle 23 1234DB toyota
[Code]....
After running VBA code
FK PLATE NO Vehicle FK PLATE NO Vehicle FK PLATE NO Vehicle 23 1234DB toyota 23 98544DB ford 34 34322SW hyundai 21 34344TR nissan 451 43434FR toyota 39 43423TY nissan 39 34445FE ford 39 43455ER nissan 39 343444E landrover 189 44455E nissan
I am working to make a graph with a few different variable and how to best visualize and format the data.
What I am trying to show is
1. The amount Provided 2. The amount used broken out in these categories - Contracts - Travel - Equipment - Other
Was originally thinking a pie chart, but would really like to make it a bar chart showing the amounts and some line on the other side showing the percentages or targets.
Dummy data provided below
Spend Plan Allocated Contracts % of Total Travel % of Total
I have a large chunk of data which I need to convert to a specific format which at first I thought was easy but im struggling to do it! I've tried concatenating and hlookup.
I want to convert this data: Name Age Test 1 Test 2
I have a plot that shows the position of a drifting buoy in the ocean. I have created a second series that is just the most recent position of that buoy. Everyday, using VBA, I update the chart 2nd series to point to the location of the new lat and long. For this second series, which is only one point, I have data labels turned on. I replace the data label text with the date and time of that latest position. Here is my problem. I can not find a way to format the date. No matter what I try, it has the format mm/dd/yyyy hh:mm AM/PM. I really just want it to be a 24 hour clock (mm/dd/yy hhmm).
I have tried forcing the format of the variable date_of_latest_pos, I have tried changing the cell format that contains the actual date and time...
i have csv data which has splits cells due to commas between numbers, and then moves data further to the right, is there a way to fix this, so for example this is what some cells look like
