Visually Inspect The Data And Then Delete Any Column Containing Irregular Data
Jun 10, 2009
I have 1000's of columns of data to go through, I just need to visually inspect the data and then delete any column containing irregular data. At the moment; I select a section of the first column of data and insert a column chart for that data. I then need to change the source of the chart data to that of the next column, to do this I click on the data within the chart and it will show the data on the worksheet that I have selected by surrounding it in a blue box, I then click on this box and drag it to the next column of data. Clicking on the blue box (which sometimes I miss, and I then have to go back to the chart to activate it again) and dragging it to the next column is getting annoying, especially as I have sooooo much data to go through. Does anyone know if there is a shortcut key for this, or how I could write a macro to do this?
View 9 Replies
ADVERTISEMENT
Aug 14, 2014
I use excel and would like to know how to copy a large volume of address data but at the same time filtering out irrelevant data placed under each other in a row, in this case, air compressors air conditioning web address etc ( see below for example). I need the first 5 lines only. The rows of unwanted data are irregular i.e some have 10 lines, others 5 , and others 2 or one line which makes using a formula difficult as there is no consistency. The data eventually need to be placed horizontally in columns to be compared to other address lists. To make matters worse, the text data has been merged and wrapped.
BDD LIMITED
3 Telford Place
L*****r QLD 4315
Phone: 07 5777 3622
View 14 Replies
View Related
Jul 18, 2013
im looking for a way to sort some data exported from an old stock recording software. the system outputs the data as a .csv file but each item bought is grouped into a particular heading, a line is only generated if money is spent under that particular heading. i cannot make the software generate lines if no money is spent so the number of lines generated each period changes.
period 1
3202 fuel £3000
3203 PPE £250
3204 major units £5000
3205 planned maint £212
3207 unplanned maint £457
period 2
3202 fuel £2500
3203 PPE £120
3204 major units £1000
3208 storage £212
3210 windows £457
if the data was regular i could simply add the totals each month and divide to get the average.
what i am looking for is a way to auto sort the data onto lines by possibly injecting the missing headings with zero as the total spent.
View 1 Replies
View Related
Mar 1, 2010
i have written a macro to parse data in to four columns using the text to columns fixed width option, but unfortunately the data i get changes its spacing and configuration every day, meaning that the fixedwidth columns dont separate the data correctly. below is how it looks some of the time.
46632hac5 Jpmcc 2007-ld12 a5 19,340,005 315
0738qac5 bscms 2007-pw17 a5 23,142,005 265
61746wcz5 msdwc 2000-prin a5 3,600,005 305
32108hp75 bacm 2007-2 a5 2,000,005 465
46630edf5 lbubs 2006-c1 a5 1,000,005 285...............
View 9 Replies
View Related
May 29, 2014
I have set of data Pasted in 4th row, in the top row 44 columns values assigned i want move data from set of data to different column among these 44 columns
Like "Service Order ID" is 1 column in set of data ,it move to second column of top row
Some of column need to delete. (Service Order Type,Service Order Description,Created By,Status,Contact,Expected Delivery Date,
Creation Date,Priority,Net Value,Currency,External Reference,Reference Date)
I want Get output result in same sheet (Actual).
View 3 Replies
View Related
Mar 28, 2014
I need to find the data in the sheet SKU & match it to the data in import_215 column F if it matches then delete the entire row (see Attachment SKU3)
View 12 Replies
View Related
Mar 30, 2007
i wrote the below which deletes few data from a particular column, but then its not working when the data increases, i mean yesterday i had data upto 12000 columns and today i had more than this....still macro deleted the same as it did yesterday........is there way where its the last filled row and deletes the data present in the column P.
Sub Deleteddata()
'
' Deleteddata Macro
' Macro recorded 3/30/2007 by kaliappk
'
Selection.End(xlDown).Select
Range("O65536").Select
Selection.End(xlUp).Select
Range("P14842").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("P14841").Select
Selection.End(xlUp).Select
Columns("O:O").Select
Selection.Copy
Columns("P:P").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("O1").Select
End Sub
View 9 Replies
View Related
Nov 7, 2008
What im trying to do is to be able to delete the row and shift cells up if there is data in column E.
I.E lets say e45 has data in it I then need row 45 deleted and rows shifted up.
Also is there anyway I can create a macro that can do this but password protect it.
View 9 Replies
View Related
Jul 28, 2013
with deleting duplicate entries in a spreadsheet that contains two names, a code number and a date. All rows with a duplicate number in column “B” should be deleted. Write a micro code? I have a few thousand of entries that need to be knocked out.
A sample file is attached : Example.xls
View 5 Replies
View Related
Nov 12, 2013
I have a spreadhseet that has rows with headers, blanks & total lines throughout that need to be deleted. There are also about 48 rows of data at the bottom that are not needed.
The row numbers will be variable each time the report is run. I need the macro to find the last row of data in column A, use this as the last row in the range and delete all rows above this unless there is numbers 1 to 12 in column J.
View 9 Replies
View Related
Jul 23, 2007
We have a client that provided us with a text file that we imported into
Excel. Rather than have the entire record in one row, they have the record
in 4 rows.
What I need a macro or something to do is move award 2 award 3 and award 4 into the same row and after doing this delete the other rows.
Below is an idea how this looks.
1JohnSmithstreetcitystateZIPaward 1
2JohnSmithstreetcitystateZIPaward 2
3JohnSmithstreetcitystateZIPaward 3
4JohnSmithstreetcitystateZIPaward 4
5BillJonesstreet2city2state2ZIP2award 1
6BillJonesstreet2city2state2ZIP2award 2
7BillJonesstreet2city2state2ZIP2award 3
8BillJonesstreet2city2state2ZIP2award 4
What we need for our data to work is:
1JohnSmithstreetcitystateZIPaward 1award 2award 3award 4
2BillJonesstreet2city2state2ZIP2award 1award 2award 3award 4
View 9 Replies
View Related
Mar 20, 2014
Is there a way to delete any text or formula from an entire row, based on column A? In my spread sheet I have multiple rows where column A is blank, but column's F, G, and H have formulas in them. I would like to use VB or any other method that would be best to clear the contents of all the rows where column A is blank.
View 6 Replies
View Related
Nov 17, 2006
I've got this macro which works well, but I have to edit it to change the column I want to check for dupes. I don't mind doing that, but now I have to share the macro with someone who is not comfortable changing the references in the code every time they run it.
As it is it checks column G:G (7) for dupes and deletes the row. Good.
I'd like for it to do the same thing, but for whatever column the active cell is in.
I can use this:
Col = ActiveCell.Column
in place of the "7" in the With Range part
but I don't know how to replace the "G1:G".
Sub DeleteDuplicatesColumnG()
With Application
.ScreenUpdating = False
.EnableEvents = False
View 9 Replies
View Related
Jan 13, 2009
I have a spreadsheet that has thousands of rows of data with maybe 6 columns.
I would like to remove the duplicate rows but the problem I am facing is that I would like to use column A as the criteria.
There are some duplicates in column A but the issue is that the data in column B, C, D, etc may not be the same for each duplicate.
Easier to see in this image:
http://img127.imageshack.us/img127/6364/datajv6.jpg
So A1 is the same as A6
A1 has data in B1 and C1, no data in D1
A6 has data in B1, C1 and D1
I need to delete either row A1 or row A6, it is irrelevant which row it is but one of them has to go.
View 2 Replies
View Related
Nov 29, 2006
How can I delete an entire row based on seperate column data? Example: I want Excel to look at cell A2 and cell E2. If the values are the same I want Excel to delete row 2.
View 4 Replies
View Related
Nov 7, 2012
I would like to delete a column using data validation list. for example :
i have a data validation INPUT list in cell A1 (Part No., Supplier 1, Supplier 2, Supplier 3) the heading start from B2,C2,D2,E2,F2. If user select Supplier 1 from data validation list in cell A1, the column Supplier 1 [c2] should be deleted.
And/or also if in cell B1 have a data validation INPUT row list (Part no.) & if user select part no.,
the selected part no. row should be deleted.
View 1 Replies
View Related
Jun 30, 2014
In my Excel 2010 workbook I wen through File > Info > Check for Issues > Inspect document. But as it's shown following, it doesn't show anything. Why?
Capture.jpg
View 5 Replies
View Related
Dec 6, 2006
I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
Sub UpdateItem
...
.Fields.Item(1).value = activecell 'activecell value = "Joseph"
If Not isempty(activecell.offset(0,1)) Then
.Fields.Item(2).value = activecell.offset(0,1).value
Else
.Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works
End If
...
End Sub
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty
' or
.Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
View 2 Replies
View Related
Sep 18, 2009
I have two sheets, a data sheet with all our customers by ref, name and spend; and a presentation sheet.
In the presentation sheet I want to display the ref, name and spend of the Top 50 customers by spend, price high to low.
The workaround:
Copy all data from data sheet, sequence top down by spend, manually delete all after 50.
My only issue is that each month the data sheet will update and I want the Top 50 to auto update, without performing the workaround above.
Is there a way to do this without VBA i.e. pivot tables etc
View 10 Replies
View Related
Apr 2, 2014
I have a lot of rows of data all divided with an empty row and if they are 6 or less all rows need to be entirely deleted any 7 or above need to be kept.
View 2 Replies
View Related
Dec 10, 2009
I have 3 columns of data that are being copied from a .csv file. The data is pasted into Columns B, C, & D. Columns C and D are hidden. So that the user does not have to unhide the columns in order to delete the data, I would like to make it so that when the data in column B is deleted, the data in columns C and D is also deleted.
View 14 Replies
View Related
Apr 11, 2008
I have a table that I would like to format visually to enhance it. I know about conditional formatting, but this doesn't seem capable of what I want to do.
Basically, I have a table, and the contents of it range from A6:H59. I want the formatting to affect an entire row based on two factors.
As an example, we shall use row 6.
If cell A6 isn't blank, and cell H6 IS blank, then I would like the entire row from A6:H6 to be coloured in Amber.
If cell A6 isn't blank, and cell H6 isn't blank, then I would like the entire row from A6:H6 to be coloured in Green.
Of course, I want this formatting to the true of all rows in the sheet, but do I need to have a conditional formatting code for each row? Does conditional formatting even go into things this deep?
I have contemplated using VBA, which I know is more than capable. There was an example given in another thread, but my VBA is poor as I haven't programmed a bean in years, and I don't know where to even start. Below is the example code take from another thread similar to this (I didn't want to hijack another man's problem).
Option Compare Text 'A=a, B=b, ... Z=z
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range
On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) ..........................
View 9 Replies
View Related
Jan 25, 2010
I need to add a space in front of a string of numbers/letters, but it still doesn't seem to match what's in the lookup range. Granted, i get the lookup range from HQ, so there may be a formatting issue.
View 2 Replies
View Related
Jun 19, 2014
I am trying to write a formula that shows the best grade for each each student and the subject in which
that grade was achieved.
In a previous post i was shown how to identify the highest grade
MAX(IF(A$3:A$34=A7,C$3:C$34))
I hit CTRL, SHIFT & ENTER to activate the formula - Result in column E
However, I also want to identify the subject in which they scored their highest grade in column F
I have two problems
1. I don't know how to write a formula that brings in the subject based on the grade for each student
2. I don't know how to write a formula in case there is a tie (see DAVID L)
View 11 Replies
View Related
Dec 12, 2013
I need a formula to automatically transfer data in a column into another column, omitting cells in the 1st column that do not have data in them.
So, for example, transfer the data in column "A" below to column "C" below omitting any blanks when the formula automatically copies data over:
Example Spreadsheet.xlsx
View 3 Replies
View Related
Oct 6, 2009
I would first like to know if there is a free or low cost add-in for MS Excel to visually show a dashboard with dials or something like that.
View 2 Replies
View Related
Oct 6, 2013
I have two columns of data and what I would like to do is have excel look over one column ( I5-I379) and for each cell that has say 30 in it I want it to return the value from a corresponding column (G5-G379), to form a separate column.......is this possible?
View 9 Replies
View Related
Jul 15, 2014
I have a table in the format below with about 3500 rows
Column A
Column B
0001
All vehicles, Retirements
0002
All vehicles, Retirements, Addition
0003
All vehicles, Retirements, Addition, Deletion from Y
I would like to change it to the following format:
Column A
Column B
0001
All vehicles
0001
Retirements
0002
All vehicles
0002
Retirements
0002
Addition
0003
All vehicles
0003
Retirements
0003
Addition
0003
Deletion from Y
View 3 Replies
View Related
Nov 14, 2008
Id like to apply a formula, any formula to an entire column if it contains data, and incorporate the original data in the calculation and then replace the original data with the result. I don't want to have to create new columns.
I'm using this to fix up database results; a common problem is dates in dot format e.g. 14.11.2008
All I have so far is an autofill formula that overwrites everything. Can someone help me with the rest? I'm using the SUBSTITUTE function to replace the dots '.' with slashes '/'
Sub Create_formula_result()
Dim Limit As Long
Dim r As range
Set r = range("A1")
r.FormulaR1C1 = _
"=IF(RC[0]"""",(SUBSTITUTE(RC[0],""."",""/"")+0) ,"""")"
Limit = ActiveSheet.UsedRange.Rows.Count
r.AutoFill Destination:=range(r, Cells(Limit, r.Column))
End Sub
View 9 Replies
View Related
Mar 7, 2008
I have a excel worksheet with the following columns: First name, Last Name, Email address, domain of email, product type, date registered. The list consists of about 50,000 entries. I want to sort the list by the domain of email(which I am able to do already) Once this is done, I want to find all instances of where a domain appears at least 10 times on the list(such as webmessenger.com appears 40 times, so I want to get that data).
For those instances where the domain appears at least 10 times, I want to pull those rows out of the intial list and put them in a new list(the new list will be sorted by domain and will only have people who have a domain which appears at least 10 times). To make this a bit more clear, The initial list I have is a list of people who registered to use the software my employer makes. We are trying to locate companies which may have many people using our consumer version of the software. When there is a large amount of people in the same organization using our software, it would benefit them to upgrade to the enterprise version due to enhanced managment features. By running this filter, I can see which companies have at least 10 users registered to use our software. Of course I will remove any Gmail/yahoo mail/msn/hotmail... pretty much any public email domains and just leave the ones that are obviously corporate emails.
So far, I think it probably has to be done with a pivot table... I was able to get a table that tells me how many instances occur from each domain, but I cannot get it to display the actual data(it just says IE. company.com 200, yahoo 120, etc... I need it to show me the 200 rows of company.com emails and extract them to a new sheet so that I can then follow up with company.com and see if they are interested in the corporate version.)
View 2 Replies
View Related
