Using Excel and VBA to clean up data

[Mrfinance123]

Hi all,

I am working on a program for a firm in which they want me to clean up the data i already have so that it is shown sequentially in each cell without having blank cells. What I mean is that since we are using a certain criteria to extract certain values that we need from a different worksheet, some cells are "blank", and what i need is the cells that contain the useful data to be in order right after the other in each cell without having "blanks" like it currently does. (Note: The cells are not actually blank hence the "" but instead it has a formula but if the data does not meet the criteria i have set it so that the cell would be blank.) VBA coding to do this is preferable but excel would also be useful.

Example:
CXX K













MRK130518C00043000 43



MRK130518C00045000 45
MRK130518C00045500 45.5
MRK130518C00046000 46


















MRK130622C00045000 45
MRK130622C00046000 46
MRK130622C00047000 47

MRK130622C00049000 49

Thank You

 

[PGAC]

How about creating a macro that sorts the data on the column in question?

 

[SkipVought]

Why do you need VBA?

Is this a task that you will perform periodically?

Does this column caontain both literals and formulas?

Data cleanup, generally speaking, is a very labor intensive task that depends on the nature of the data in each row. It usually takes itterative process where you 1) do an analysis of the data that is present and 2) determine a strategy for isolating each class of problem and correcting that class.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Mrfinance123]

Hello all,

Thanks for the multiple replies, I believe a VBA code would be ueful in this situation because the data is constantly changed and does not always meet the format that I displayed above. For example there may be more or less data points. The two columns contain only formulas and are used to calculate information that is valuable to the firm, What they have asked me to do here is to bring together all these useful points in a column next to the final one so that we can easily view the data without having to manually scroll and look for the data as this would be done hundreds of times a day and for different assets where the prices differ greatly. I hope this cleared up any questions you had.

 

[SkipVought]

So what happens when you sort?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

Hmm isn't it a simple case of Autofilter on the column to exclude "blanks"

Or if you really want the list without blanks in a " in a column next to the final one" then use advanced filter.

Or if you just want to delete the rows with blanks then filter to show only the blanks and then delete the visible rows

Whichever you do maybe pop a button on the sheet to run the VBA code that does it.

Gavin

 

[zelgar]

Since this sheet is getting information from other sheets, wouldn't it be easier to make a macro to populate this sheet based on the criteria that all of the data is available and not having blanks in the first place?

Otherwise, are you asking for ways to hide or delete the entries that are blank?