Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Getting Rid of Blank Cells
Greetings to Excel Pros:
I have a worksheet that has a label in Column A. There are five more columns (B, C, D, E, F) that have data in them. The label in cell A1 is "John". Cell B1 is John's Hourly wage "$16.50". This is where it gets tricky. Cell C2 is John's hours per week "40". Cell D3 is John's 401K deductions "$250". As you can see from this example, the data pertaining to John is staggered as I go down the columns. I have several people in the same spreadsheet that have data staggered in this fashion. Not all of them have the same information with the same spacing. Mary for example only has hourly wage info and hours per week info. I want to get rid of the blank cells so that all of the data pertaining to an individual is on the same row as the person's name. Any ideas??? I hope this was a clear explaination. Chris |
#2
|
|||
|
|||
ChrisA wrote:
|| Greetings to Excel Pros: || || I have a worksheet that has a label in Column A. There are five more || columns (B, C, D, E, F) that have data in them. The label in cell || A1 is "John". Cell B1 is John's Hourly wage "$16.50". This is || where it gets tricky. Cell C2 is John's hours per week "40". Cell || D3 is John's 401K deductions "$250". As you can see from this || example, the data pertaining to John is staggered as I go down the || columns. I have several people in the same spreadsheet that have || data staggered in this fashion. Not all of them have the same || information with the same spacing. Mary for example only has hourly || wage info and hours per week info. I want to get rid of the blank || cells so that all of the data pertaining to an individual is on the || same row as the person's name. Any ideas??? || || I hope this was a clear explaination. || || Chris It will be a bit laborious, but if you right-click on a blank cell, and choose "Delete" you will get several options as to whether you want to move cells left, right, or up and down. Go through the list doing that until you get all the data correct! You will have to do the reverse at some points and insert cells! -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk |
#3
|
|||
|
|||
Yeah, doing it manually would take me weeks. I have a lot of data. There
must be some macro or some clever equation that could move the appropriate data to correct location. I have about 25,000 rows of data for about 10,000 people (averaging about five rows of data per person.) Any other ideas? "Gordon" wrote: ChrisA wrote: || Greetings to Excel Pros: || || I have a worksheet that has a label in Column A. There are five more || columns (B, C, D, E, F) that have data in them. The label in cell || A1 is "John". Cell B1 is John's Hourly wage "$16.50". This is || where it gets tricky. Cell C2 is John's hours per week "40". Cell || D3 is John's 401K deductions "$250". As you can see from this || example, the data pertaining to John is staggered as I go down the || columns. I have several people in the same spreadsheet that have || data staggered in this fashion. Not all of them have the same || information with the same spacing. Mary for example only has hourly || wage info and hours per week info. I want to get rid of the blank || cells so that all of the data pertaining to an individual is on the || same row as the person's name. Any ideas??? || || I hope this was a clear explaination. || || Chris It will be a bit laborious, but if you right-click on a blank cell, and choose "Delete" you will get several options as to whether you want to move cells left, right, or up and down. Go through the list doing that until you get all the data correct! You will have to do the reverse at some points and insert cells! -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk |
#4
|
|||
|
|||
Do this on a copy of your data
Sub CopyNames() Dim rng As Range Dim strName As String Dim lngRow As Long Dim x As Integer Application.ScreenUpdating = False strName = "" lngRow = 1 For Each rng In Selection If Len(rng.Text) 0 Then lngRow = rng.Row Else For x = 1 To 4 If Len(rng.Offset(0, x).Text) 0 Then Cells(lngRow, x + 1).Value = rng.Offset(0, x).Value rng.Offset(0, x).Clear End If Next End If Next End Sub "ChrisA" wrote: Yeah, doing it manually would take me weeks. I have a lot of data. There must be some macro or some clever equation that could move the appropriate data to correct location. I have about 25,000 rows of data for about 10,000 people (averaging about five rows of data per person.) Any other ideas? "Gordon" wrote: ChrisA wrote: || Greetings to Excel Pros: || || I have a worksheet that has a label in Column A. There are five more || columns (B, C, D, E, F) that have data in them. The label in cell || A1 is "John". Cell B1 is John's Hourly wage "$16.50". This is || where it gets tricky. Cell C2 is John's hours per week "40". Cell || D3 is John's 401K deductions "$250". As you can see from this || example, the data pertaining to John is staggered as I go down the || columns. I have several people in the same spreadsheet that have || data staggered in this fashion. Not all of them have the same || information with the same spacing. Mary for example only has hourly || wage info and hours per week info. I want to get rid of the blank || cells so that all of the data pertaining to an individual is on the || same row as the person's name. Any ideas??? || || I hope this was a clear explaination. || || Chris It will be a bit laborious, but if you right-click on a blank cell, and choose "Delete" you will get several options as to whether you want to move cells left, right, or up and down. Go through the list doing that until you get all the data correct! You will have to do the reverse at some points and insert cells! -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk |
#5
|
|||
|
|||
Chris
Try selecting columns B through F and F5SpecialBlanksOK Now EditDeleteShift cells up. Should get what you need. Gord Dibben Excel MVP On Wed, 11 May 2005 12:54:01 -0700, ChrisA wrote: Yeah, doing it manually would take me weeks. I have a lot of data. There must be some macro or some clever equation that could move the appropriate data to correct location. I have about 25,000 rows of data for about 10,000 people (averaging about five rows of data per person.) Any other ideas? "Gordon" wrote: ChrisA wrote: || Greetings to Excel Pros: || || I have a worksheet that has a label in Column A. There are five more || columns (B, C, D, E, F) that have data in them. The label in cell || A1 is "John". Cell B1 is John's Hourly wage "$16.50". This is || where it gets tricky. Cell C2 is John's hours per week "40". Cell || D3 is John's 401K deductions "$250". As you can see from this || example, the data pertaining to John is staggered as I go down the || columns. I have several people in the same spreadsheet that have || data staggered in this fashion. Not all of them have the same || information with the same spacing. Mary for example only has hourly || wage info and hours per week info. I want to get rid of the blank || cells so that all of the data pertaining to an individual is on the || same row as the person's name. Any ideas??? || || I hope this was a clear explaination. || || Chris It will be a bit laborious, but if you right-click on a blank cell, and choose "Delete" you will get several options as to whether you want to move cells left, right, or up and down. Go through the list doing that until you get all the data correct! You will have to do the reverse at some points and insert cells! -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk |
#6
|
|||
|
|||
Duke:
I think you are onto something there. It doesn't run exactly as I had hoped but I think its a start. The macro that you wrote moves all of the data from right to left. I want to just the opposite. Wherever there is a name, I want all of the information pertaining to that name to be moved up to the row where the name exists. The trick is that not all of the names have the same information. For example, one person may have some data that is in column D where the person below them on the spreadsheet does not have any information in that column. In a case like this I just want a blank cell to appear in the column ofn the row of the person that does not have any information. I just want all of the data for the person to be on the same row rather than staggered over 3 to 6 rows. Any ideas? "Duke Carey" wrote: Do this on a copy of your data Sub CopyNames() Dim rng As Range Dim strName As String Dim lngRow As Long Dim x As Integer Application.ScreenUpdating = False strName = "" lngRow = 1 For Each rng In Selection If Len(rng.Text) 0 Then lngRow = rng.Row Else For x = 1 To 4 If Len(rng.Offset(0, x).Text) 0 Then Cells(lngRow, x + 1).Value = rng.Offset(0, x).Value rng.Offset(0, x).Clear End If Next End If Next End Sub "ChrisA" wrote: Yeah, doing it manually would take me weeks. I have a lot of data. There must be some macro or some clever equation that could move the appropriate data to correct location. I have about 25,000 rows of data for about 10,000 people (averaging about five rows of data per person.) Any other ideas? "Gordon" wrote: ChrisA wrote: || Greetings to Excel Pros: || || I have a worksheet that has a label in Column A. There are five more || columns (B, C, D, E, F) that have data in them. The label in cell || A1 is "John". Cell B1 is John's Hourly wage "$16.50". This is || where it gets tricky. Cell C2 is John's hours per week "40". Cell || D3 is John's 401K deductions "$250". As you can see from this || example, the data pertaining to John is staggered as I go down the || columns. I have several people in the same spreadsheet that have || data staggered in this fashion. Not all of them have the same || information with the same spacing. Mary for example only has hourly || wage info and hours per week info. I want to get rid of the blank || cells so that all of the data pertaining to an individual is on the || same row as the person's name. Any ideas??? || || I hope this was a clear explaination. || || Chris It will be a bit laborious, but if you right-click on a blank cell, and choose "Delete" you will get several options as to whether you want to move cells left, right, or up and down. Go through the list doing that until you get all the data correct! You will have to do the reverse at some points and insert cells! -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk |
#7
|
|||
|
|||
Try this against a copy of your worksheet--it destroys the original data.
Option Explicit Sub testme01() Dim wks As Worksheet Dim TopCell As Range Dim BotCell As Range Dim myRng As Range Dim LastRow As Long Set wks = Worksheets("sheet1") With wks Set myRng = .UsedRange 'just try to reset last used cell. LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row Set TopCell = .Range("a1") Do If IsEmpty(TopCell.Offset(1, 0)) = False Then Set BotCell = TopCell Else 'botcell is one up from the next name Set BotCell = TopCell.End(xlDown).Offset(-1, 0) If BotCell.Row = .Rows.Count - 1 Then Set BotCell = .Cells(LastRow, "A") End If End If If BotCell.Row = TopCell.Row Then 'do nothing--a single row group Else Set myRng = Nothing On Error Resume Next With .Range(TopCell, BotCell) Set myRng = .Offset(0, 1).Resize(.Rows.Count - 1, 3) _ .Cells.SpecialCells(xlCellTypeBlanks) End With On Error GoTo 0 If myRng Is Nothing Then 'no gaps--do nothing Else myRng.FormulaR1C1 = "=if(r[+1]c="""",na(),r[+1]c)" With TopCell.EntireRow .Value = .Value End With End If End If Set TopCell = BotCell.Offset(1, 0) If TopCell.Row LastRow Then 'we're done Exit Do End If Loop 'now clean up column A On Error Resume Next .Range("a:a").Cells.SpecialCells(xlCellTypeBlanks) .EntireRow.Delete On Error GoTo 0 'now clean the na's .Cells.Replace what:="#n/a", replacement:="", _ lookat:=xlWhole, MatchCase:=False End With End Sub This is the important line: Set myRng = .Offset(0, 1).Resize(.Rows.Count - 1, 3) _ .Cells.SpecialCells(xlCellTypeBlanks) It finds a group, then looks at all the rows in that group except the last. If you need more than 3 columns, just change that 3 to something else. It fills the empty cells in that range with formulas that look at what's below--except for that last row in the group. ChrisA wrote: Greetings to Excel Pros: I have a worksheet that has a label in Column A. There are five more columns (B, C, D, E, F) that have data in them. The label in cell A1 is "John". Cell B1 is John's Hourly wage "$16.50". This is where it gets tricky. Cell C2 is John's hours per week "40". Cell D3 is John's 401K deductions "$250". As you can see from this example, the data pertaining to John is staggered as I go down the columns. I have several people in the same spreadsheet that have data staggered in this fashion. Not all of them have the same information with the same spacing. Mary for example only has hourly wage info and hours per week info. I want to get rid of the blank cells so that all of the data pertaining to an individual is on the same row as the person's name. Any ideas??? I hope this was a clear explaination. Chris -- Dave Peterson |
#8
|
|||
|
|||
Gord - That's ingenious!!
"Gord Dibben" wrote: Chris Try selecting columns B through F and F5SpecialBlanksOK Now EditDeleteShift cells up. Should get what you need. Gord Dibben Excel MVP On Wed, 11 May 2005 12:54:01 -0700, ChrisA wrote: Yeah, doing it manually would take me weeks. I have a lot of data. There must be some macro or some clever equation that could move the appropriate data to correct location. I have about 25,000 rows of data for about 10,000 people (averaging about five rows of data per person.) Any other ideas? "Gordon" wrote: ChrisA wrote: || Greetings to Excel Pros: || || I have a worksheet that has a label in Column A. There are five more || columns (B, C, D, E, F) that have data in them. The label in cell || A1 is "John". Cell B1 is John's Hourly wage "$16.50". This is || where it gets tricky. Cell C2 is John's hours per week "40". Cell || D3 is John's 401K deductions "$250". As you can see from this || example, the data pertaining to John is staggered as I go down the || columns. I have several people in the same spreadsheet that have || data staggered in this fashion. Not all of them have the same || information with the same spacing. Mary for example only has hourly || wage info and hours per week info. I want to get rid of the blank || cells so that all of the data pertaining to an individual is on the || same row as the person's name. Any ideas??? || || I hope this was a clear explaination. || || Chris It will be a bit laborious, but if you right-click on a blank cell, and choose "Delete" you will get several options as to whether you want to move cells left, right, or up and down. Go through the list doing that until you get all the data correct! You will have to do the reverse at some points and insert cells! -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk |
#9
|
|||
|
|||
That's a very good solution, but I don't think it works for the OP's problem.
This doesn't keep the data "grouped" correctly. Depending on how the data looks, you could be sliding data for the last person into the first person's group. Duke Carey wrote: Gord - That's ingenious!! "Gord Dibben" wrote: Chris Try selecting columns B through F and F5SpecialBlanksOK Now EditDeleteShift cells up. Should get what you need. Gord Dibben Excel MVP On Wed, 11 May 2005 12:54:01 -0700, ChrisA wrote: Yeah, doing it manually would take me weeks. I have a lot of data. There must be some macro or some clever equation that could move the appropriate data to correct location. I have about 25,000 rows of data for about 10,000 people (averaging about five rows of data per person.) Any other ideas? "Gordon" wrote: ChrisA wrote: || Greetings to Excel Pros: || || I have a worksheet that has a label in Column A. There are five more || columns (B, C, D, E, F) that have data in them. The label in cell || A1 is "John". Cell B1 is John's Hourly wage "$16.50". This is || where it gets tricky. Cell C2 is John's hours per week "40". Cell || D3 is John's 401K deductions "$250". As you can see from this || example, the data pertaining to John is staggered as I go down the || columns. I have several people in the same spreadsheet that have || data staggered in this fashion. Not all of them have the same || information with the same spacing. Mary for example only has hourly || wage info and hours per week info. I want to get rid of the blank || cells so that all of the data pertaining to an individual is on the || same row as the person's name. Any ideas??? || || I hope this was a clear explaination. || || Chris It will be a bit laborious, but if you right-click on a blank cell, and choose "Delete" you will get several options as to whether you want to move cells left, right, or up and down. Go through the list doing that until you get all the data correct! You will have to do the reverse at some points and insert cells! -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk -- Dave Peterson |
#10
|
|||
|
|||
Hmmm. That had excaped me Dave. Good catch.
"Dave Peterson" wrote: That's a very good solution, but I don't think it works for the OP's problem. This doesn't keep the data "grouped" correctly. Depending on how the data looks, you could be sliding data for the last person into the first person's group. Duke Carey wrote: Gord - That's ingenious!! "Gord Dibben" wrote: Chris Try selecting columns B through F and F5SpecialBlanksOK Now EditDeleteShift cells up. Should get what you need. Gord Dibben Excel MVP On Wed, 11 May 2005 12:54:01 -0700, ChrisA wrote: Yeah, doing it manually would take me weeks. I have a lot of data. There must be some macro or some clever equation that could move the appropriate data to correct location. I have about 25,000 rows of data for about 10,000 people (averaging about five rows of data per person.) Any other ideas? "Gordon" wrote: ChrisA wrote: || Greetings to Excel Pros: || || I have a worksheet that has a label in Column A. There are five more || columns (B, C, D, E, F) that have data in them. The label in cell || A1 is "John". Cell B1 is John's Hourly wage "$16.50". This is || where it gets tricky. Cell C2 is John's hours per week "40". Cell || D3 is John's 401K deductions "$250". As you can see from this || example, the data pertaining to John is staggered as I go down the || columns. I have several people in the same spreadsheet that have || data staggered in this fashion. Not all of them have the same || information with the same spacing. Mary for example only has hourly || wage info and hours per week info. I want to get rid of the blank || cells so that all of the data pertaining to an individual is on the || same row as the person's name. Any ideas??? || || I hope this was a clear explaination. || || Chris It will be a bit laborious, but if you right-click on a blank cell, and choose "Delete" you will get several options as to whether you want to move cells left, right, or up and down. Go through the list doing that until you get all the data correct! You will have to do the reverse at some points and insert cells! -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk -- Dave Peterson |
#11
|
|||
|
|||
Chris -
I think my macro does just what you want. If it finds any data in a row WITHOUT a label in column A, it moves that data upwards, in its original column, and places it on the row of the last found label in column A When it's done running, all you need to do is sort on column A Haven't looked at Dave Peterson's code. His may do the very same thing Duke "ChrisA" wrote: Duke: I think you are onto something there. It doesn't run exactly as I had hoped but I think its a start. The macro that you wrote moves all of the data from right to left. I want to just the opposite. Wherever there is a name, I want all of the information pertaining to that name to be moved up to the row where the name exists. The trick is that not all of the names have the same information. For example, one person may have some data that is in column D where the person below them on the spreadsheet does not have any information in that column. In a case like this I just want a blank cell to appear in the column ofn the row of the person that does not have any information. I just want all of the data for the person to be on the same row rather than staggered over 3 to 6 rows. Any ideas? "Duke Carey" wrote: Do this on a copy of your data Sub CopyNames() Dim rng As Range Dim strName As String Dim lngRow As Long Dim x As Integer Application.ScreenUpdating = False strName = "" lngRow = 1 For Each rng In Selection If Len(rng.Text) 0 Then lngRow = rng.Row Else For x = 1 To 4 If Len(rng.Offset(0, x).Text) 0 Then Cells(lngRow, x + 1).Value = rng.Offset(0, x).Value rng.Offset(0, x).Clear End If Next End If Next End Sub "ChrisA" wrote: Yeah, doing it manually would take me weeks. I have a lot of data. There must be some macro or some clever equation that could move the appropriate data to correct location. I have about 25,000 rows of data for about 10,000 people (averaging about five rows of data per person.) Any other ideas? "Gordon" wrote: ChrisA wrote: || Greetings to Excel Pros: || || I have a worksheet that has a label in Column A. There are five more || columns (B, C, D, E, F) that have data in them. The label in cell || A1 is "John". Cell B1 is John's Hourly wage "$16.50". This is || where it gets tricky. Cell C2 is John's hours per week "40". Cell || D3 is John's 401K deductions "$250". As you can see from this || example, the data pertaining to John is staggered as I go down the || columns. I have several people in the same spreadsheet that have || data staggered in this fashion. Not all of them have the same || information with the same spacing. Mary for example only has hourly || wage info and hours per week info. I want to get rid of the blank || cells so that all of the data pertaining to an individual is on the || same row as the person's name. Any ideas??? || || I hope this was a clear explaination. || || Chris It will be a bit laborious, but if you right-click on a blank cell, and choose "Delete" you will get several options as to whether you want to move cells left, right, or up and down. Go through the list doing that until you get all the data correct! You will have to do the reverse at some points and insert cells! -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk |
#12
|
|||
|
|||
I think that the problem is the user didn't select the correct range to start.
Select A1:A25000 (through the last used row) (When I tried your code, I must have selected the wrong range, too--'cause it didn't work for me. Today, I selected the correct range and it worked fine--What did you change! <vbg) And your code is a lot more straight forward, too! I would add something like: On Error Resume Next Range("a:a").Cells.SpecialCells(xlCellTypeBlanks). EntireRow.Delete On Error GoTo 0 To do the clean up, though. Duke Carey wrote: Chris - I think my macro does just what you want. If it finds any data in a row WITHOUT a label in column A, it moves that data upwards, in its original column, and places it on the row of the last found label in column A When it's done running, all you need to do is sort on column A Haven't looked at Dave Peterson's code. His may do the very same thing Duke "ChrisA" wrote: Duke: I think you are onto something there. It doesn't run exactly as I had hoped but I think its a start. The macro that you wrote moves all of the data from right to left. I want to just the opposite. Wherever there is a name, I want all of the information pertaining to that name to be moved up to the row where the name exists. The trick is that not all of the names have the same information. For example, one person may have some data that is in column D where the person below them on the spreadsheet does not have any information in that column. In a case like this I just want a blank cell to appear in the column ofn the row of the person that does not have any information. I just want all of the data for the person to be on the same row rather than staggered over 3 to 6 rows. Any ideas? "Duke Carey" wrote: Do this on a copy of your data Sub CopyNames() Dim rng As Range Dim strName As String Dim lngRow As Long Dim x As Integer Application.ScreenUpdating = False strName = "" lngRow = 1 For Each rng In Selection If Len(rng.Text) 0 Then lngRow = rng.Row Else For x = 1 To 4 If Len(rng.Offset(0, x).Text) 0 Then Cells(lngRow, x + 1).Value = rng.Offset(0, x).Value rng.Offset(0, x).Clear End If Next End If Next End Sub "ChrisA" wrote: Yeah, doing it manually would take me weeks. I have a lot of data. There must be some macro or some clever equation that could move the appropriate data to correct location. I have about 25,000 rows of data for about 10,000 people (averaging about five rows of data per person.) Any other ideas? "Gordon" wrote: ChrisA wrote: || Greetings to Excel Pros: || || I have a worksheet that has a label in Column A. There are five more || columns (B, C, D, E, F) that have data in them. The label in cell || A1 is "John". Cell B1 is John's Hourly wage "$16.50". This is || where it gets tricky. Cell C2 is John's hours per week "40". Cell || D3 is John's 401K deductions "$250". As you can see from this || example, the data pertaining to John is staggered as I go down the || columns. I have several people in the same spreadsheet that have || data staggered in this fashion. Not all of them have the same || information with the same spacing. Mary for example only has hourly || wage info and hours per week info. I want to get rid of the blank || cells so that all of the data pertaining to an individual is on the || same row as the person's name. Any ideas??? || || I hope this was a clear explaination. || || Chris It will be a bit laborious, but if you right-click on a blank cell, and choose "Delete" you will get several options as to whether you want to move cells left, right, or up and down. Go through the list doing that until you get all the data correct! You will have to do the reverse at some points and insert cells! -- Gordon Burgess-Parker Interim Systems and Management Accounting www.gbpcomputing.co.uk -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy down - special to fill only the blank cells | Excel Discussion (Misc queries) | |||
blank cells | Excel Discussion (Misc queries) | |||
Blank Cells in Pivot Tables | Excel Discussion (Misc queries) | |||
Non Blank - Blank Cells???? | Excel Discussion (Misc queries) | |||
In Exel 2000, stop the blank cells (with formulas) from printing. | Excel Discussion (Misc queries) |