Format Data From Web So It's Usable
Nov 8, 2009
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:..............
View 5 Replies
ADVERTISEMENT
Mar 20, 2014
I am pulling out information from a cell using =RIGHT(C2,4). It gives me 6-2 in the cell, which is what I want. Now I want to sum the cell but how to do it. I have tried converting it but it doesn't work, I tried pulling out each digit, but that didn't work.
Ideally, I would like a formula that could convert cell A1 into a result in one cell.
Attached File : converting text example.xlsx
View 7 Replies
View Related
Oct 2, 2009
if a cell contains "usable characters". By usable characters i mean i want something to check if a cell contains a number or letter.
cell = AAAAA formula returns true
cell = A123 formula = true
cell = 123 formula = true
cell = ----- formula = false
View 9 Replies
View Related
Mar 27, 2007
The code works 100% fine in Excel with no errors when the button is clicked.
However, when it transfers over the data, it is one column off to the left.
Sounds like an easy fix, eh?
Not for me. I've tried everything I could think of.
Code that takes the range and pastes it onto the other sheets in the next available cell:
For Each e In myWs
Set LastR = Sheets("" & e).Range("b" & Rows.Count).End(xlUp).Offset(1)
LastR.Resize(, 2).Value = Range("c4:d4").Value
Next
Set LastR = Nothing
I did find one solution that did bump the cell column offset over 1. It went something like this:
Set LastR = Sheets("" & e).Range("b" & Rows.Count).End(xlUp).Offset(1, 1)
Problem is, it no longer takes it to the next available cell.
It just pastes over the same cells over and over no matter how many times you try it.
View 9 Replies
View Related
Feb 11, 2009
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.
View 2 Replies
View Related
May 1, 2014
I have around 30k data. which is in invalid format.
Ex: 12987654321vinay kk 876543219
32567456789 kkccjhg fo 345678921
I want to convert this into correct format as below with start letter from
MOD, 987654321,, vinay, kk,87654219
MOD,567456789, , kkccjhg, fo,345678921
I want to know which are formulas I have to use to get this info in correct format.
View 1 Replies
View Related
Jun 18, 2014
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
[Code] .....
View 1 Replies
View Related
Feb 4, 2009
I have the following data retrieved via import data tools ...
View 9 Replies
View Related
Jun 17, 2013
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.
CALLS
PUTS
Show June, 2013 Options Hide June, 2013 Options
Symbol
StrikePrice
Vol
Last
Last
Vol
Symbol
quote
15.00
2.00
14.25
0.02
22.00
quote
[Code] ........
View 9 Replies
View Related
Apr 27, 2007
I have a small table, 6 rows, 2 columns which looks something like:
Apples 3
Pears 1
Bananas 0
Cherries 2
Oranges 1
Grapes 1
The total sum of column B will always be 8.
What I want to produce is a range, say d1:d8 which reproduces the data in a format like:
Apple
Apple
Apple
Pear
Cherry
Cherry
Orange
Grape
i.e. responding to the number of items in the former table specified.
I'm sure it can be done with some long stringed If statements mixed with counts, but do any of you have any good ideas for formulas in d1:d8?
View 9 Replies
View Related
Nov 24, 2007
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.
View 10 Replies
View Related
Feb 15, 2010
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.
View 6 Replies
View Related
May 5, 2009
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:
View 4 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
Feb 11, 2010
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:
V C 1 2 3
1 23 0.06 0.04 0.03
1 12 0.09 0.06 0.03
2 5 0.11 0.07 0.05
2 11 0.12 0.08 0.04
The text file should have the above data in the following format with brackets and comma:
(V,C,1)[value]
For example:
(1,23,1)[0.06]
(1,23,2)[0.04]
(1,23,3)[0.03]...........
View 3 Replies
View Related
Jun 15, 2012
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.
View 1 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
Aug 14, 2012
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
View 1 Replies
View Related
Oct 23, 2012
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
[Code]...
View 1 Replies
View Related
Feb 20, 2014
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
[Code]....
View 1 Replies
View Related
Mar 21, 2008
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...
ActiveChart.SeriesCollection("Latest Position").ApplyDataLabels AutoText:=True, LegendKey:= False, ShowSeriesName:=True, ShowCategoryName:=False, ShowValue:=False, ShowPercentage:=False, ShowBubbleSize:=False
ActiveChart.SeriesCollection("Latest Position").Points(1).DataLabel.Select
Selection.Characters.Text = date_of_latest_pos
Selection.AutoScaleFont = False
With Selection.Characters(Start:=1, Length:=23).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
View 9 Replies
View Related
Apr 30, 2009
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
31874.90 JPMORGAN
but need to be like this
31 874.90 JPMORGAN
View 9 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
Jun 15, 2007
with "Selecting equally spaced cells and putting the info into columns...". I work for a company that needs to get the info from this site here:
[url]
I'm trying to get it all organized into one nice and neat excel spreadsheet. I researched things myself and I found that you can just insert the source code into excel and excel will automatically put the tables into cells. My only problem is that, if you noticed, on the site each and every person has their own "First Name", or "Last name", "Email", or whatever. I just want one "First Name" in the column A, the "Last name" part in column B, and so on and so forth. Is this possible to do in an easy method because I'm seriously not going to copy and paste the info myself, all 36,000+ times.
View 9 Replies
View Related
Dec 12, 2007
I have a stacking program but it does not keep the first column associated with the data that is stacked as well as the first row. This is how the database looks now:
CREATE TABLES LIKE BELOW?July '07August '07September '07
HR #55551341,929
HR #57217311,653
HR #102322,8981,988
HR #98221,5571,097
this is what I need:
CREATE TABLES LIKE BELOW?HR #55551 July '07
HR #5721 July '07
HR #10232 July '07
HR #9822 July '07
HR #55 34 August '07
HR #57 731 August '07
HR #102 2,898 August '07
HR #98 1,557 August '07
HR #55 1,929 September '07
HR #57 1,653 September '07
HR #102 1,988 September '07
HR #98 1,097 September '07
The current VBA prgram just stacks the columns in one column going from right to left.
View 6 Replies
View Related
Nov 9, 2013
What I'm trying to do is to copy my data to another work sheet but with the different format like column to row..
item CODE
item DESCRIPTION
Cost
store 1
store2
store3
store4
store5
store6
store7
store8
[Code] .....
It includes formatting of column into row (store name,qty)
Store Name
Item Code
Item Description
Quantity
Price
Total Amount
Automatic it has total amount
store1
0003
item1
3
46.80
140.4
It will skip the column that has no value(empty cell) and will not transfer it into row
store5
0003
item1
2
46.80
93.6
[Code] .....
View 9 Replies
View Related
Feb 11, 2014
I'm looking at what appears to be a custom chart. It is a box divided into 4 equal quadrants. In each quadrant is a percentage with the total equaling 100%. In each of the quadrants there is fill equal to the percent that is numerically written in that quadrant. I'll post a picture with this, but if I was talking cells then....
A1 = 15% B1 = 66%
A2 = 4% B2 = 15%
The backround image behind the number is filled up by volume equal to the percentage indicated. They are also color coded with red representing the highest percentage quadrant filled.
To top that, there are little arrows along the sides of each quadrant indicating an average score.
My first question is WHAT TYPE OF CHART IS THIS THING!, and second can this be done in excel?
View 4 Replies
View Related
Apr 18, 2014
I am trying to limit the format of data to all cells in a column to 2 letters followed by 6 digits. The letters and the numbers are not required to be the same, but the person entering can only enter in that format.
View 2 Replies
View Related
Aug 15, 2008
I have a text file with a long list of data that simply needs to be extracted under separate columns in excel. Below is one sample out of a long list. Here is sample data and the format of the text ...
-----------------------------------------------------------
ABC MORTGAGE LLC
232 23RD ST STE A
BAKERSFIELD, CA 93301
KERN County
Title II
Approval Date: Dec 08, 2007 [11 Areas Approved for Business]
HECM: No Originates 203K: No
Telephone: (343) 633-1234 FAX Number: (343) 632-1330
E-Mail Address: chadlow3@aol.com
------------------------------------------------------------
In excel I need to have these columns, Company Name, Address, Phone, Fax, and Email. I need to pull the data out of the text file automatically and insert it into these columns. The other information in the sample such as approval date is not needed. How do I do this?
View 14 Replies
View Related
