Excel Sorting Blank Cells as if they have a Value 1

[KiwiRiccardo]

Hi all, hope that someone can help with this before my laptop takes the short way from the 9th floor;-)

Right I have a workbook called "Central Data" with 2 worksheets lets call them "Linked Data" and "Text Values"

"Linked Data" is sectioned into groups of 100 rows that link to other workbooks with the following formula

In column A this links to a reference number in the original workbook

=+IF([Workflow.xls]Milestones!C5="","",[Workflow.xls]Report!$H$4)

From Column B this links to data in the original workbook.

=+IF([Workflow.xls]Milestones!C5="","",[Workflow.xls]Milestones!C5)

Both of these formula are dragged across and down to create an array of linked data. Fine.

As stated earlier there is a different workbook linked for every 100 rows in the "Central Data" workbook. This has been arranged in this way because the number of rows of data in the linked workbooks will vary but never exceed 100 rows.

Problem happens when I want to put this data into a format that is easy for review.

Created a simple macro to copy the data from "Linked Data" and past as values in "Test Values". Then sort by Column A which happens to have an alpha numeric reference. My formula show any cell in the oWhen the sort ascending is done Excel thinks that there is data in the blank cells that should be at the top of the sort order.

Help!! Please.

 

[xlbo]

Unfortunately, ther eIS data in the blank cells. The data is a zero length dtring i.e. ""

The ascii value of a zero length string is very small so it always gets sorted 1st (ascending)

You may be better off setting the missing ones to #N/A rather than "" as this will always get sorted to the end of an alpha sort.

eg
=+IF([Workflow.xls]Milestones!C5="",#N/A,[Workflow.xls]Milestones!C5)

You can then use conditional formatting to turn the text white for any cell that has #N/A (or delete them after the sort)



Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[KiwiRiccardo]

Thank you that has worked well. I have used a find and replace to delete all of the #N/A's and this seems to have done the trick so I can now sort my data at will.

Cheers

Richard

30 degrees in London today!!

 

[KiwiRiccardo]

Actually I have just completed my macro and looked at the code and infact we are replacing the "#N/A" with a "" (see below) and yet I can now sort my "visible" data as I would expect to be able to. Seems a bit odd but no doubt there is an answer.

Ric


Cells.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Select

 

[xlbo]

There seems to be a difference between entering a "" as part of a formula and "" as part of a find/replace - probably to do with how VBA interacts with the spreadsheet. That's about as much as I can give you for now though.

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question