Removing Columns With No Data
Aug 7, 2012
I am working on importing contacts from Zimbra to Outlook/Exchange.
Outlook is not properly importing the contacts and is consistently importing only some of the data for each contact. So, I played around a lot with the import file and have found that if I delete out all the columns that have no data in them for any contact record, that Outlook imports no problem, perfectly.
So...here's my issue. I have multiple contact lists for each user that I am importing. We're talking about 100 different CSV files to sort through, some of which have at least 500 records (rows) in them. It is a lot of scrolling up and down left and right to find columns manually/visually that have no data in them.
Any manner in which I could add a row at the top of each CSV/excel file, input a formula, drag it across all columns that will tell me if there is data within that row? I tried using a filter, but because a lot of the data columns have no data in the first row across multiple columns, it doesn't seem to be accurate.
View 1 Replies
ADVERTISEMENT
Aug 13, 2013
I have a list of about 85,000 addresses and I know that there are about 35,000 duplicates in it.
If I do 'Remove duplicates' it deletes them but takes the first one of each it finds and what I want to do is remove the duplicate with no UPRN in it (Column B)
View 6 Replies
View Related
Oct 2, 2013
I have trying to remove duplicate value for two columns which are not in order since long time. I have listed an example for the purpose. Two rows contains same information in different columns. I just wanted to have one row and remove the other with same information.
AAAA
500135500136
500136500135
500137500138
500138500137
500191500192
500192500191
500277500278
500278500277
500413500414
500414500413
View 5 Replies
View Related
Dec 10, 2008
I have a single spreadsheet with a few columns but hundreds of rows. Each row is a property reference (with other data such as address in the columns) with a type of charge in column J and the amount in column K. I'll try to replicate below ...
A B ....... J K
Prop1 Prop1Address Rent 100
Prop2 Prop2Address Rent 150
Prop3 Prop3Address Gas 70
Prop4 Prop4Address Water 60
Prop4 Prop4Address Rent 200
Prop4 Prop4Address Elec 80
Prop5 Prop5Address Service 90
I want the sheet to display a property on a single row so would look like as follows ...
A B ....... Rent Gas Water Elec Service Total
Prop1 Prop1Address 100 100
Prop2 Prop2Address 150 150
Prop3 Prop3Address 70 70
Prop4 Prop4Address 200 60 80 340
Prop5 Prop5Address 90 90
View 9 Replies
View Related
Jun 21, 2006
Need to find out how I can restrict a worksheet to show only the columns and rows that I want. For example, I want to show columns A to H, and rows 1 to 25, and nothing else - I don't want there to be a column G, or a row 26, just blank grey space. I know it's possible, because I've seen it done : D . But the closest I can get to is: View > Page Break Preview, which isn't quite what I'm after.
View 2 Replies
View Related
Jul 20, 2013
I have a list A2...A11
In cell B1 is the following formula: =SUM(IF(FREQUENCY(IF($A$2:$A$11"",MATCH("~"&$A$2:$A$11&"",$A$2:$A$11&"",0)),Rvec),1))
Rvec is defined in NAME MANAGER as: =ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1
In cells B2...B11 is this formula: =IF(ROWS($B$2:B2)
View 6 Replies
View Related
May 26, 2014
I am trying to create a straight column list that can take the rows and columns of a table, and list only the nonblank items. The formula I am using only seems to work with one column, not multiple.
Formula:
[Code] .....
View 12 Replies
View Related
Nov 1, 2011
I have a combination chart with data from Sun thru Sat (7 days). My manager wants to display only the weekday data (M,T,W,Th,F) and remove the weekend data (Sat,Sun). However, I also need this data to be included in other charts I need. I was thinking in years past, that I was able to remove specific data from the chart directly without having to alter or change the spreadsheet data? I have copied a second set of data and hid the weekend data, however, I have gaps in the data, which I need to remove in the chart display. Not sure what to do, or the best avenue?
View 2 Replies
View Related
Dec 11, 2013
Currently I've been able to remove the prefixes and suffixes using the replace option as those were consistent across the data set, I'm now left with a series of numbers that look like this:
11.225D2
11.24c1
11.28.r1
11.31.1.2h1
11.36.g1
11.39 A2
11.39B1
11.3c1
In most cases I need to remove the information after the last period. Some of the data has a space at the end, instead of a period and then some have no space or period but have the letter that needs to be dropped. I'm using xl 2011 for MAC.
View 6 Replies
View Related
Feb 17, 2012
In my spreadsheet, column A, I have a list is part #'s. I have found at the end of my part #'s there are several spaces in each cell. I need to remove these from the cells so that my formulas will be easier to use. Is there a way to do this? The part #'s very in length and format. They would look like this "100020x0 ".
View 2 Replies
View Related
Oct 10, 2007
I need to give the facility for a line to be edited (Columns A to E would remain as-is, only F would change. I also need to give the facility for a line to be removed completely.
Private Sub CommandButton1_Click()
Dim shDest As Worksheet
Dim shSource As Worksheet
Dim Rw As Long
Set shDest = Sheets("BLOCKTIMES")
Set shSource = Sheets("DATA ENTRY")
Rw = shDest.Range("B65536").End(xlUp).Row + 1
With shDest
'B2 to column B; D4 to Column C; C3 to Column D...........
View 9 Replies
View Related
Mar 22, 2013
I have a data sheet which I need to filter in some way with multiple criteria. For example
I have a sheet called Training which has 4000 rows of data. Column F is labelled Cost ID and under this column I have 400 different IDs of which I need to see data for 155 out of the 400.
Is there a simple way of removing the unwanted data?
View 5 Replies
View Related
Apr 3, 2014
I have data in the form of a table. For example a list of duplicate names, each of these names have a corresponding date (lease expiry date). The problem is that the duplicate names have varying dates. So the remove duplicates function does not work because i need to remove the duplicate names with the older dates. I want the latest dates to remain behind.
Data Currently: Solution should be:
Sue1/3/2014Mike8/8/2014
Jay25/4/2013Sue1/3/2014
Jay25/4/2013Jay25/4/2013
Mike8/8/2014
Mike8/8/2014
Sue1/3/2014
Sue25/6/2012
Sue1/3/2014
Sue5/7/2012
Jay2/2/2011
Mike5/5/2010
View 1 Replies
View Related
May 28, 2014
How to remove all the " symbol out of a string of data?
The " symbol appears at different places and i need to remove them all completely. the Find & Replace tool doesn't work because when you have say 3/4" and want it to just read 3/4 it automatically converts it to 03-Apr. even when you have changed the cell formatting to text.
View 12 Replies
View Related
Jun 10, 2014
I'm trying to find out the rule for de-duplicating data. I am removing duplicates based on an identification number in a data set of about 6000 records, including the duplicates (some records appear about 4 times). Due to the nature of the data I'm working with, there are only a handful of records that are "true" duplicates, i.e. some of the records appear 4 times but there is a difference in terms of location, etc and some are true duplicates in that there is no difference.
I need to know how Excel removes duplicates - does it only keep the first line that it finds for that identification number? Also, is there a way that I could create a rule for it to keep the record with the highest rate for example?
View 3 Replies
View Related
Dec 4, 2008
I have some data that is seperated by spaces. I need to split the data so each is in a seperate cell and then remove the spaces. I can do this if there is only one space inbetween the data, but that is not always the case. I attach a workbook with examples.
View 6 Replies
View Related
Apr 22, 2009
I have a worksheet with columns A to E which hold the following data:- Week No, Budget Code, Budget Description, Name and Hours. However, as you can see in the attached spreadsheet there is not always a value in the hours field.
What I want to achieve is to automatically exclude any of the rows of data in the Pivot table that includes zero in the hours field (such as the rows highlighted in yellow) and also not to show if/when the pivot table is refreshed.
View 3 Replies
View Related
Nov 13, 2009
I'm doing some simple data extraction, e.g.
A B
1 bob 3
2 mandy 4
3 charlie 6
4 dave 1
5 steve 5
So I had in c1 to c5 = =if(b1 > 3, a1,"") autofilled, which works fine, but I end up with,
gap
mandy
charlie
gap
steve
how would I get,
mandy
charlie
steve
also is it possible to have an if statement in 1 cell change the value of another cell?
e.g.
in a1
if(b1>5, c1="yes",c1="no"), can't seem to get it to work
View 14 Replies
View Related
Jan 24, 2013
removing empty rows from my set of data.
I have a big set of data that I'm copying and pasting into excel. When I paste it there are 2 empty rows between each line of data. I want to remove these empty rows, without affecting the data. I tried 'Go to special...' and deleting all empty cells and stuff but I couldn't get it to work. The problem is that there are blank cells in some of the rows that I want to keep. When I try to remove the blank rows, I also end up removing rows of data that I want to keep.
I've made an example for you to try and explain it better - attached.
remove all of the rows that are completely empty, but leave any row that has any data in it (at any point), that would be awesome!
View 4 Replies
View Related
Feb 17, 2014
I have this Macro that removes the selected items from the listbox1 and sheet called "Form" the only problem with that is that I have another sheet with a formulas in it that referes to the cells/ranges in sheet "Form" and so when I remove the range in "Form" worksheet the range still exists in my other worksheet where the formulas are and this is causing the errors.
What modifications I need to make to my macro to be able to delete data/ranges from sheet "Form" and sheet "Training Cost" ? so if I delete range A5:D5 in sheet Form it will also delete range A5:D5 in Training Cost.
[Code] ......
View 2 Replies
View Related
Mar 12, 2014
I have several fields in a row that contain names of files e.g. 123.xlsx. Some fields will contain file names that will be duplicates of each other and some will be blank entries (although the blank entries can be changed to a value such as 'n/a' or 'no' etc if required).
I require only the non duplicate values to appear in the final cell, each separated with ';'.
My data is in row 2 of a spreadsheet and in every other column (A,C,E,G,I,K,M...for 45 instances in total).
I have used the following formula to identify the unique values (example below for the first four cells):
=A2&IF(C2=A2,"",","&C2)&IF(OR(E2=A2,E2=C2),"",","&E2)&IF(OR(G2=A2,G2=C2,G2=E2),"",","&G2)
This works well and if there are several blank entries then I use a SUBSTITUTE function to change the multiple ',,,,' to a single ';'. So I only see the unique file names in the final cell, separated with ';'.
However, the above formula becomes longer and longer when each cell is added to it. I have over 40 cells that need to be added and I wondered if there was a better way of doing this?
View 6 Replies
View Related
Mar 30, 2009
I need to remove any rows containing "EEE" in the size column.
I also need to remove any discontinued ("D" in the "Type" column)styles that are currently not in inventory. For example 05-5490 does not have any available in either width "D" or "EW" and should be removed; however, 28-112 does have stock and should remain in the spreadsheet. Anything with an "F" in the "Type" column should be left alone.
View 13 Replies
View Related
Feb 27, 2014
I run a report each week and would like to combine it with my Updated Members workbook and then remove duplicates.
Is there a way to add this new data (ActiveList.xlsx ) to the last row of data of my (UpdatedMembers.xlsx) Sheet and remove duplicates. (I would like the new additions to stay on the bottom of my original data in the UpdatedMembers sheet, so that I can add notes to their row)
I am still learning my way around excel and all of my attempt to do this have been unsuccessful. how to pull this off.
View 1 Replies
View Related
Mar 28, 2005
I need to remove any data which is say 20% above or below the overall observed data, in order to compute simple High, Low and Average excel function calculations.
Is there any way to remove such outliers when calculating averages, lows, highs etc?
View 2 Replies
View Related
Oct 10, 2011
Currently trying to solve a niggling issue with removing duplicate row data from competition entry databases using the following code...
Code:
Sub remove_duplicates()
Dim lastrow As Long
Dim i As Long, j As Long
On Error GoTo errhandler
Columns("A:G").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
[Code] ......
errhandler:
'MsgBox Err.Description
End Sub
What I need to happen is for duplicates to be based on both the name and email matching (columns A + B) and then remove all duplicate rows, whilst ignoring but preserving the content in columns C,D,E and F on those rows that remain. Column G contains either "Yes" or "No" depending on a users membership status. Sometimes duplicated data will contain identical name and email address but have different membership status in column G where someone was logged in when submitting a form the first time, and then logged out the next (potentially a mixture of the two over multiple times). Therefore I need any duplicate users who have different values in the G column throughout the database to be detected by the macro and we must ensure that the single row retained in the final non-duplicated list displays "Yes" in column G for that user.
Duplicate users who are always "yes" or always "no" work fine, its only the ones with mixed values for column G that need to be analysed and their return forced as Yes in the final list.
The final list should preserve the original column structure and thus be only one row per unique user.
The above code appeared to work the first time with a sample file, but when running the macro on one of my full size lists (3k - 5k rows) it returns 2 rows for each user who started off as a mixture of Yes/No... ?
Both rows will say yes in column G (so that part is correct!) but I have to re-run the macro again on the same dataset for it to remove the duplicate yes rows to get the list as it finally should be!
So I can achieve the result by running it twice, but this is a bit of a cheating way to do it and also the next stage after this involves something similar across multiple worksheets so I need something rock solid to build upon!
View 5 Replies
View Related
Feb 9, 2012
I have a 5 column document with about 3000 entries that I need streamlining. THe first column is a student's name, then grade. The next 2 columns are for Honour Roll Standing (A or B) and/or an Effort Honour Roll Standing (E). Finally there is also a "term" column indicating if they received this award is Term 1, 2 or 3.
An example source file is found here: [URL]...
As you can see, there are many students who have received an award in all 3 terms, however there are also some students that only received standing for 1 or 2 terms.
I need to manipulate the data so that there are no duplicate names and all the data regarding Honour Roll standings and which term it occured in are all laid out in one row per student.
IDEAL COLUMN HEADINGS:
NAME | Grade | Term1 HonourRoll | Term1 Effort | Term2 HonourRoll | Term2 Effort |
etc etc etc
View 9 Replies
View Related
May 15, 2012
I have created a dynamic chart but I want the axis to only reflect data greater than 0. Can the axis' of a chart also be dynamic with the dynamic chart?
Upon request...Will email spreadsheet if need to review.
View 1 Replies
View Related
Jul 12, 2012
I have a workbook with a Pivot Table full of user data and security reports. The table is structured in a way that shows, numerically; each username and how man roles are assigned. I need to remove the username/row combo's that are listed as 1 or less. Obviously if I simply highlight the fields I want to delete it won't work. How would I go about removing such rows?
View 2 Replies
View Related
Jul 17, 2012
If I update my data, then refresh the pivot table, old values still show as an option. How can I delete these items? If it matters, my pivot table's name is PivotTable2.
View 1 Replies
View Related
Feb 25, 2007
I have an excel sheet with approx 30,000 rows of data.
These are all keyword phrases.
They can be related to any subject, but for this example these are related to the root keyword phrase of "car Rent"
I have some software which basically pulls in keyword phrases from search engines and meta tags etc, including misspelled keyword phrases.
My problem;
The data is uncleaned.
In other words there might be I think it's called "Carriage return" data in there, so the row of data might be very deep (Instead of a row height of say 10.5 it could be anything, IE some could be 100 or 200 even).
There are unwanted characters, for example; ()[]{}+?!""^*
(If it could delete all unwanted characters except for letters/digits)
There is a problem I see, that if it removes _ or - between words, that it will join the words together which won't be of any use. If it deletes anything with a letter either side of it or a letter and digit, or 2 digits, 1 either side it would then need to add a space to replace the hyphen.
So for example; if there was a phrase in the list like ....
View 9 Replies
View Related
