Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SAP to Excel. Removing blank cells HELP!!!!
I down load from SAP to Excel quite often. There is one facet I use and I
ALWAYS have a ton of blank cells. I need to remove them but keep a structure to the data. Here is a rough idea of what I am working with...... Mind you this there is this type of info througout the spreadsheet. So if I delete cells and it asks to shift up or left.. it brings all the data together. ANY IDEAS????? 10000XXXX Joe Schmoe 10000XXXY Joe Schmoe 1234567 The Store 1234568 The Store 1234569 The Store 1234510 The Store 1234511 The Store 1234513 The Store 1234514 The Store 1234515 The Store 1234516 The Store 1234517 The Store 1234518 The Store |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SAP to Excel. Removing blank cells HELP!!!!
One easy play which might appeal to you
Insert a new col A Put in A2: =IF(COUNTA(B2:IV2),"x","") Copy A2 down to cover extent of data Apply autofilter on col A, filter out: x Copy n paste the filtered results into a new sheet, delete col A And that should give you the exact results you seek in a matter of seconds -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "DestinySky" wrote: I down load from SAP to Excel quite often. There is one facet I use and I ALWAYS have a ton of blank cells. I need to remove them but keep a structure to the data. Here is a rough idea of what I am working with...... Mind you this there is this type of info througout the spreadsheet. So if I delete cells and it asks to shift up or left.. it brings all the data together. ANY IDEAS????? 10000XXXX Joe Schmoe 10000XXXY Joe Schmoe 1234567 The Store 1234568 The Store 1234569 The Store 1234510 The Store 1234511 The Store 1234513 The Store 1234514 The Store 1234515 The Store 1234516 The Store 1234517 The Store 1234518 The Store |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SAP to Excel. Removing blank cells HELP!!!!
Questions:
How do you bring the data into Excel? Do you copy/paste, or read in from a ..txt or .csv file? What should the data look like when you're done? Eric --------------------- If toast always lands butter-side down, and cats always land on their feet, what happen if you strap toast on the back of a cat and drop it? Steven Wright (1955 - ) "DestinySky" wrote: I down load from SAP to Excel quite often. There is one facet I use and I ALWAYS have a ton of blank cells. I need to remove them but keep a structure to the data. Here is a rough idea of what I am working with...... Mind you this there is this type of info througout the spreadsheet. So if I delete cells and it asks to shift up or left.. it brings all the data together. ANY IDEAS????? 10000XXXX Joe Schmoe 10000XXXY Joe Schmoe 1234567 The Store 1234568 The Store 1234569 The Store 1234510 The Store 1234511 The Store 1234513 The Store 1234514 The Store 1234515 The Store 1234516 The Store 1234517 The Store 1234518 The Store |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SAP to Excel. Removing blank cells HELP!!!!
I would create 3 €˜Helpercolumns.
From the data youve supplied, it looks like your data comes over in 6 columns, A thru F. - Insert 3 columns to the left of the data so that columns A, B and C are blank. - In Column A, put the formula =ROW() This will keep track of the order that your rows are currently in - In column B, put the formula =CONCATENATE(D1,E1,F1) Assuming that you are starting in row 1 - In column C, put the formula =CONCATENATE(G1,H1,I1) Assuming that you are starting in row 1 Columns B and C will combine the blank cells with the cells that have data in them. Copy these 3 formulas down to the end of your data. - Using the Paste Special function, make these formulas into values. - Sort on Column B. This will put all blank rows at the top. - Delete the blank rows. - Re-sort on Column A. This will put the data back into it's original order. -Delete Columns D thru I. This deletes the original data with it's many blank cells that you don't want. -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "DestinySky" wrote: I down load from SAP to Excel quite often. There is one facet I use and I ALWAYS have a ton of blank cells. I need to remove them but keep a structure to the data. Here is a rough idea of what I am working with...... Mind you this there is this type of info througout the spreadsheet. So if I delete cells and it asks to shift up or left.. it brings all the data together. ANY IDEAS????? 10000XXXX Joe Schmoe 10000XXXY Joe Schmoe 1234567 The Store 1234568 The Store 1234569 The Store 1234510 The Store 1234511 The Store 1234513 The Store 1234514 The Store 1234515 The Store 1234516 The Store 1234517 The Store 1234518 The Store |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SAP to Excel. Removing blank cells HELP!!!!
If there's the possibility of invisible white spaces, formula blanks: ""
within the supposedly "blank" rows, use this heavier duty workhorse instead in A2: =IF(SUMPRODUCT(--(TRIM(B2:IV2)<"")),"x","") -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SAP to Excel. Removing blank cells HELP!!!!
it imports as a .txt file and it should look like this:
10000XXXX Joe Schmoe 10000XXXY Joe Schmoe 1234567 The Store 1234568 The Store 1234569 The Store 1234510 The Store 1234511 The Store 1234513 The Store 1234514 The Store 1234515 The Store 1234516 The Store 1234517 The Store 1234518 The Store "EricG" wrote: Questions: How do you bring the data into Excel? Do you copy/paste, or read in from a .txt or .csv file? What should the data look like when you're done? Eric --------------------- If toast always lands butter-side down, and cats always land on their feet, what happen if you strap toast on the back of a cat and drop it? Steven Wright (1955 - ) "DestinySky" wrote: I down load from SAP to Excel quite often. There is one facet I use and I ALWAYS have a ton of blank cells. I need to remove them but keep a structure to the data. Here is a rough idea of what I am working with...... Mind you this there is this type of info througout the spreadsheet. So if I delete cells and it asks to shift up or left.. it brings all the data together. ANY IDEAS????? 10000XXXX Joe Schmoe 10000XXXY Joe Schmoe 1234567 The Store 1234568 The Store 1234569 The Store 1234510 The Store 1234511 The Store 1234513 The Store 1234514 The Store 1234515 The Store 1234516 The Store 1234517 The Store 1234518 The Store |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
SAP to Excel. Removing blank cells HELP!!!!
Oh, OK,
Forget about the concatenate functions in my other post. Just use the =Row( ) formula in Column A. I thought you wanted the blank column cells as well as blank rows deleted. -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "DestinySky" wrote: it imports as a .txt file and it should look like this: 10000XXXX Joe Schmoe 10000XXXY Joe Schmoe 1234567 The Store 1234568 The Store 1234569 The Store 1234510 The Store 1234511 The Store 1234513 The Store 1234514 The Store 1234515 The Store 1234516 The Store 1234517 The Store 1234518 The Store "EricG" wrote: Questions: How do you bring the data into Excel? Do you copy/paste, or read in from a .txt or .csv file? What should the data look like when you're done? Eric --------------------- If toast always lands butter-side down, and cats always land on their feet, what happen if you strap toast on the back of a cat and drop it? Steven Wright (1955 - ) "DestinySky" wrote: I down load from SAP to Excel quite often. There is one facet I use and I ALWAYS have a ton of blank cells. I need to remove them but keep a structure to the data. Here is a rough idea of what I am working with...... Mind you this there is this type of info througout the spreadsheet. So if I delete cells and it asks to shift up or left.. it brings all the data together. ANY IDEAS????? 10000XXXX Joe Schmoe 10000XXXY Joe Schmoe 1234567 The Store 1234568 The Store 1234569 The Store 1234510 The Store 1234511 The Store 1234513 The Store 1234514 The Store 1234515 The Store 1234516 The Store 1234517 The Store 1234518 The Store |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
SAP to Excel. Removing blank cells HELP!!!!
Or, if you want a Visual Basic solution (much faster once you have it!) -
here is a routine that Nigel posted a while ago in the Programming group. I you're not familiar with Visual Basic in Excel, I can add instructions on installing and using the routine. ' ' Remove all blank rows from data on a worksheet ' Sub RemoveEmptyRows() Dim xr As Long, xc As Integer, dRow As Boolean Dim CalcType As Long With Application .ScreenUpdating = False CalcType = .Calculation .Calculation = xlCalculationManual End With For xr = Val(StrReverse(ActiveSheet.UsedRange.Address)) To 1 Step -1 dRow = True For xc = 1 To 255 If Len(Trim(Cells(xr, xc))) 0 Then dRow = False Exit For End If Next xc If dRow Then Rows(xr).Delete shift:=xlUp Next xr With Application .ScreenUpdating = True .Calculation = CalcType End With End Sub -- ------------------- If toast always lands butter-side down, and cats always land on their feet, what happen if you strap toast on the back of a cat and drop it? Steven Wright (1955 - ) "Gary Brown" wrote: Oh, OK, Forget about the concatenate functions in my other post. Just use the =Row( ) formula in Column A. I thought you wanted the blank column cells as well as blank rows deleted. -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "DestinySky" wrote: it imports as a .txt file and it should look like this: 10000XXXX Joe Schmoe 10000XXXY Joe Schmoe 1234567 The Store 1234568 The Store 1234569 The Store 1234510 The Store 1234511 The Store 1234513 The Store 1234514 The Store 1234515 The Store 1234516 The Store 1234517 The Store 1234518 The Store "EricG" wrote: Questions: How do you bring the data into Excel? Do you copy/paste, or read in from a .txt or .csv file? What should the data look like when you're done? Eric --------------------- If toast always lands butter-side down, and cats always land on their feet, what happen if you strap toast on the back of a cat and drop it? Steven Wright (1955 - ) "DestinySky" wrote: I down load from SAP to Excel quite often. There is one facet I use and I ALWAYS have a ton of blank cells. I need to remove them but keep a structure to the data. Here is a rough idea of what I am working with...... Mind you this there is this type of info througout the spreadsheet. So if I delete cells and it asks to shift up or left.. it brings all the data together. ANY IDEAS????? 10000XXXX Joe Schmoe 10000XXXY Joe Schmoe 1234567 The Store 1234568 The Store 1234569 The Store 1234510 The Store 1234511 The Store 1234513 The Store 1234514 The Store 1234515 The Store 1234516 The Store 1234517 The Store 1234518 The Store |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
SAP to Excel. Removing blank cells HELP!!!!
Try the below to delete all blank rows. If you are new to macros Set the
Security level to low/medium in (Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11. From menu 'Insert' a module and paste the below code. Save. Get back to Workbook. Run macro from Tools|Macro|Run <selected macro() Sub DeleteBlankRows() For lngRow = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1 If WorksheetFunction.CountBlank(Rows(lngRow)) = Columns.Count _ Then Rows(lngRow).Delete Next End Sub If this post helps click Yes --------------- Jacob Skaria "DestinySky" wrote: I down load from SAP to Excel quite often. There is one facet I use and I ALWAYS have a ton of blank cells. I need to remove them but keep a structure to the data. Here is a rough idea of what I am working with...... Mind you this there is this type of info througout the spreadsheet. So if I delete cells and it asks to shift up or left.. it brings all the data together. ANY IDEAS????? 10000XXXX Joe Schmoe 10000XXXY Joe Schmoe 1234567 The Store 1234568 The Store 1234569 The Store 1234510 The Store 1234511 The Store 1234513 The Store 1234514 The Store 1234515 The Store 1234516 The Store 1234517 The Store 1234518 The Store |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
SAP to Excel. Removing blank cells HELP!!!!
Max,
PERFECT!!!! THANK YOU THANK YOU!!! :o)) "Max" wrote: One easy play which might appeal to you Insert a new col A Put in A2: =IF(COUNTA(B2:IV2),"x","") Copy A2 down to cover extent of data Apply autofilter on col A, filter out: x Copy n paste the filtered results into a new sheet, delete col A And that should give you the exact results you seek in a matter of seconds -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "DestinySky" wrote: I down load from SAP to Excel quite often. There is one facet I use and I ALWAYS have a ton of blank cells. I need to remove them but keep a structure to the data. Here is a rough idea of what I am working with...... Mind you this there is this type of info througout the spreadsheet. So if I delete cells and it asks to shift up or left.. it brings all the data together. ANY IDEAS????? 10000XXXX Joe Schmoe 10000XXXY Joe Schmoe 1234567 The Store 1234568 The Store 1234569 The Store 1234510 The Store 1234511 The Store 1234513 The Store 1234514 The Store 1234515 The Store 1234516 The Store 1234517 The Store 1234518 The Store |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
SAP to Excel. Removing blank cells HELP!!!!
Welcome, glad it worked well for you.
-- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:300 Subscribers:70 xdemechanik --- "DestinySky" wrote in message ... Max, PERFECT!!!! THANK YOU THANK YOU!!! :o)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing blank cells from a column | Excel Worksheet Functions | |||
Removing blank cells... | Excel Discussion (Misc queries) | |||
Removing blank cells | Excel Worksheet Functions | |||
Removing blank cells in a column | Excel Worksheet Functions | |||
removing blank cells from a column | Excel Discussion (Misc queries) |