Patty Bottorff
asked on
Fill in blank cells so data can be sorted
I am not sure how to explain this, so please bear with me. I don't know what this would be called, but...
I work for a school district and have a spreadsheet with discipline data that I must sort. Unfortunately, when the data was exported to Excel, some of the data was merged within several rows of cells. I removed the merge from the data, and it left blank cells. I need to sort the data, but I must populate the blank cells first.
So, let's say row 2 has discipline data for student A. The data is several columns wide. Student A has 8 rows of data, some of which is duplicate data (of which I need to remove dups). Below all of that, student B has 10 rows of data. The problem is, after the merge was removed, row 2 contains student A name and ID, along with one discipline record. Rows 3-9 contain blanks where the student A name and ID should be, along with one discipline record per row. Row 10 contains student B name and ID, along with one discipline record. How do I populate rows 3-9 with the student A name and ID? I have been doing copy and paste to fill these blanks in, but ... I have 35 thousand rows to work with. Ugghh.
I am attaching a sample of what the spreadsheet looks like (just a small portion).
I would be thankful for any help. Like I said, I don't even know what to call this so I can search for help.
Thank you,
Patty
Excel-Sample.xlsx
I work for a school district and have a spreadsheet with discipline data that I must sort. Unfortunately, when the data was exported to Excel, some of the data was merged within several rows of cells. I removed the merge from the data, and it left blank cells. I need to sort the data, but I must populate the blank cells first.
So, let's say row 2 has discipline data for student A. The data is several columns wide. Student A has 8 rows of data, some of which is duplicate data (of which I need to remove dups). Below all of that, student B has 10 rows of data. The problem is, after the merge was removed, row 2 contains student A name and ID, along with one discipline record. Rows 3-9 contain blanks where the student A name and ID should be, along with one discipline record per row. Row 10 contains student B name and ID, along with one discipline record. How do I populate rows 3-9 with the student A name and ID? I have been doing copy and paste to fill these blanks in, but ... I have 35 thousand rows to work with. Ugghh.
I am attaching a sample of what the spreadsheet looks like (just a small portion).
I would be thankful for any help. Like I said, I don't even know what to call this so I can search for help.
Thank you,
Patty
Excel-Sample.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think you need this. Click the button called "Clean Data" on the Test Sheet to run the code to get the desired output. The code will clean the data of the ActiveSheet.
Sub CleanData()
Dim lr As Long
Application.ScreenUpdating = False
lr = ActiveSheet.UsedRange.Rows.Count
On Error Resume Next
Range("B2:I" & lr).SpecialCells(xlCellTypeBlanks).Formula = "=R[-1]C"
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
Columns("B").NumberFormat = "@"
Columns("D").NumberFormat = "@"
Columns("H:I").NumberFormat = "@"
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9), Header:=xlYes
Application.ScreenUpdating = True
End Sub
Shouldn't need VBA to accomplish this.
Select the data region and apply an Autofilter.
On column A now use the dropdown to select filter criteria; deselect the Select All box so that all ticks disappear and then scroll to the bottom of the list and select Blanks, click OK. This will now show all the blanks in column A and the relevant discipline records in columns B and beyond.
In column A, I guess the first visible row will be row 3; row 1 headers, row 2 populated with the first student, row 3 is the same student but with no name in column A. In A3 type the formula =A2 which will then populate that cell with the student name from row 2. Now select the remainder of column A and press Ctrl + D. This will fill down the column but will only populate the cells that are visible.
Use the Filter dropdown to then Show All and you can copy and paste the column as values to overwrite the formulas.
Select the data region and apply an Autofilter.
On column A now use the dropdown to select filter criteria; deselect the Select All box so that all ticks disappear and then scroll to the bottom of the list and select Blanks, click OK. This will now show all the blanks in column A and the relevant discipline records in columns B and beyond.
In column A, I guess the first visible row will be row 3; row 1 headers, row 2 populated with the first student, row 3 is the same student but with no name in column A. In A3 type the formula =A2 which will then populate that cell with the student name from row 2. Now select the remainder of column A and press Ctrl + D. This will fill down the column but will only populate the cells that are visible.
Use the Filter dropdown to then Show All and you can copy and paste the column as values to overwrite the formulas.
ASKER
Thank you all!
ASKER