Thread: Filtering

Good Morning / Afternoon,

I am currently working on a new project which requires a fair bit of data analysis work on one massive excel spreadsheet.
I know its easy to filter information but there is a trick I wish to perform with this filtering situation.

Excel Sheet:
Basically the columns go from A - DI
Basically the rows go from 1 - 200
Not every cell has information / data in it so blanks are literally throughout the spreadsheet
There is no unique identifier column. (Oh yes the fun!)

The annoying part of this sheet / project is for my current task I only require 3 of the massive amounts of columns.
I am trying to set up a new spread sheet (within the same workbook) that basically does a massive filter of all the information when a certain package is selected.

I have attached a small example file to show what I am trying to do.

Raw Data (sheet)- All the data itself I need to filter down.
Filter (sheet) - The outcome I am desiring.

I basically am trying to get it so the procedure would be.
Open Filter Sheet which Online contains Headers (Work No., Surname, Degree)
You click the Work No. Headline and it brings downs all the unique (Work No.'s)
Select a number (example Work 1) and the following result is received.

Can anyone help with this problem?

Morning Venkat1926

Originally Posted by venkat1926

I am sending the file renamed "ssam modified.xlsm" the macro in this is modified., see sheet "filtered" This is AFTER running the macro "test"

you can retest it by again running "test"

In regards to this, this in itself is a quite successful option which i can utilize, the only problem is it deletes / removes blank entries? In saying this i mean if there is a line of separation between data it complete removes the data below that line which cannot be allowed. As I said at the start there is quite a few "empty" cells in the datasheet as its not fully up to to date. I cannot have it delete all the remaining info below each "empty cell". Is there a way to make a final group that organizes the "blank" cells into their own group like the others do?




Morning snb,

I tried to implementin your code below but came up with fatal error of "invalid" and it crashed the datasheet?
And I know i'm very rough / new to this VB side of the program but i'm finding it hard to even follow what your code is doing!

Originally Posted by snb

Alternative

Sub snb()
 With Sheets("raw master")
  .Columns(5).AdvancedFilter 2, , .Cells(1, 20), True
  sn = .Cells(1, 20).CurrentRegion.Offset(1).SpecialCells(2)
  .Columns(20).ClearContents
    
  .Range("A1,D1").EntireColumn.Hidden = True
    
  With .Cells(1).CurrentRegion
   For Each cl In sn
    .AutoFilter 5, cl
    .Copy Sheets("filtered").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    .AutoFilter
   Next
  End With
 End With
  
 with Sheets("filtered").Cells(2, 1).CurrentRegion
   .Resize(, 3) = Application.Index(.value, [row(1:100)], Array(3, 1, 2))
 end with
End Sub

Can anyone offer any help, I am very new to this type of VBA scripting and i cannot get it to register blanks or implement into the new worksheet successfully

Online_Help.xlsm

For example I can't see a way past the following problems'

not exceeding the range?
and including blanks so that the remaining does not get deleted?

I have included a example upload to help communicate what I mean?

hahaha, sorry snb I just quoted to define the two posts i was reply two. I will not do it again!

I also did not try your suggestion as I seriously could not follow it. I inserted it in like you suggested but a error came up straight away so I left it and went back to the orginal code that Venkat was helping me with.
This point i'm just lost and can't even get it back to work originally!

I konw its annoying but if you could help or comment it would be greatly appreciated its very important and i've already wasted the majority of this week just trying to get it to work successfully!

I've noticed that thanks onErrorGoto0 but only snb and venkat has helped so far and their code appears to be one extreme to another lol

Okay! I have made progress in regards to including the "Empty Cells and Organising them" the code is now at the current level

Sub test()
Dim rdata As Range, filt As Range, dest As Range
Dim uniquework As Range, cwork As Range, rwork As Range, work As String
undo
Worksheets("Raw Master").Activate
Set rwork = Range(Range("E1"), Range("E1").End(xlDown))
Set uniquework = Range("A1").End(xlDown).Offset(5, 0)
Set rdata = Range("A1").CurrentRegion
rwork.AdvancedFilter xlFilterCopy, , uniquework, True
Set uniquework = Range(uniquework.Offset(1, 0), uniquework.End(xlDown))
For Each cwork In uniquework
work = cwork.Value

rdata.AutoFilter field:=5, Criteria1:=work
Set filt = rdata.SpecialCells(xlCellTypeVisible)
filt.Copy Worksheets("Filtered").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

ActiveSheet.AutoFilterMode = False
Next cwork
With Worksheets("Filtered")
.Range("a1").EntireColumn.Delete
.Range("A1:B1").EntireColumn.Cut .Range("E1")
.Range("A1:C1").EntireColumn.Delete
End With
Range(Range("a1").End(xlDown).Offset(1, 0), Cells(Rows.Count, "A")).EntireRow.Delete
End Sub

Thankyou for your help Venkat but now when I try to implement the code into using another column for Unique purposes / work groups it says the items are out of range? Can anyone offer any help?
I have tried Using the specific Column name "DI1" and "113 - the numerical value" but with all tests it shoots up a out of range error? I have done some good searches and come across similar problems? Is this error unable to be fixed due to the size of the sheet itself? so some basic "re-arrangement" might fix it? IE placing the information in column "DI" into "E" etc on another sheet before running the organising method the only way to do this?