Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 220
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 236
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 236
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 220
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 16
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Removing blank cells from a column brenty Excel Worksheet Functions 4 December 29th 06 04:07 PM
Removing blank cells... Jambruins Excel Discussion (Misc queries) 0 March 31st 06 08:02 PM
Removing blank cells Dallas101 Excel Worksheet Functions 0 March 1st 06 03:59 PM
Removing blank cells in a column Dallas101 Excel Worksheet Functions 0 February 22nd 06 01:03 AM
removing blank cells from a column joeeng Excel Discussion (Misc queries) 1 July 26th 05 08:01 PM


All times are GMT +1. The time now is 04:19 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"