Filtering

[Ssam87]

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?

[venkat1926]

the file is sent back now named "ssam.xls"
your original I have called "filered original"
a new sheet is added "Filtered"
in this sheet you shall see the result of the macro

the two macros are in the vbeditor in the module

run only "test"

the macros are repeated for referece

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Ssam87]

Okay So I have used your code and implemented it into the actual example excel file.
(Attached to this post below as Ssam)

This has worked but it always searches for Work 1 due to the macro command?
I was trying to set it up so that when one was to go to the filtered worksheet and selected "any work group... IE Work 2" it would fresh populate that information on the "Filtered" sheet?

I'm trying to make this as automatic as possible due to users on it.

The command in macro also is limited to 5 not a calculations method? I'm trying to configure this code myself cause I did it a while ago but i'm not having so much success with that part yet but I know i can get that to work again.

Is there a way to have it set up the way I am trying or is it not possible?

*Updated with the excel file.

[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"

[snb]

Alternative

Please Login or Register  to view this content.

[Ssam87]

Morning 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!

Alternative

Please Login or Register  to view this content.

[Ssam87]

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

[Ssam87]

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?

[snb]

Please do not quote (see the forum rules)

Did you try my suggestion in the file Venkat posted ?
VBA is like any other language: start with the fundamentals first.

[OnErrorGoto0]

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!

Brevity is often the antithesis of legibility.

For a VBA beginner, snb's code generally is akin to asking someone learning English as a third language to read the Canterbury Tales in their first lesson. I suggest you read point #2 of his signature and ignore the code.

[Ssam87]

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

[Ssam87]

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

Please Login or Register  to view this content.

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?