Automatically and continuously removing blanks in data range

**joshnathan** · 07-04-2012, 02:09 PM

Hello i have a data range of two coloums and which are checking against another set of two col's with the same set of data to see if it fits between a range of numbers.
As not all of them meet the crieteria i end up having a list with lots of blanks in them. As i have to do this for 50 seperate sheets is there a way to make it automatically delete all the blanks within a set of cells. So that it automatically moves everything to the top. problem is this data is always changing so the c and d cols might have 30 vals in now but could have 50. and i need all the data that matches to move up to the top of H and I so i can gather more info on them from an external source.

Hope this make sense

tester book.xls

**Steffen Thomsen** · 07-04-2012, 02:17 PM

The try sorting the data, blanks will go to the end

Please Login or Register  to view this content.

**joshnathan** · 07-04-2012, 02:22 PM

Hi just to confirm this will just reorder the data in the coloums so the blank ones go to the end ? will it still pair up B & C? for example C4 and B4 will still be side by side? Also how would i fit this in with the if statement already in there e.g. = =IF(AND($D20 >= $C$2, $D20 <= $D$2), $D20, "")

**Steffen Thomsen** · 07-04-2012, 02:28 PM

It will fit nice, though right now it will only sort column a and b.
How many columns is there?

**joshnathan** · 07-04-2012, 02:36 PM

At the Moment i only have two columns that need sorting which are H and I and they need to be sorted between I5:I60 & J5:j60 however I5 &j5 must always be next to each other if this makes sense. As the data that each of the cells in I and J is pulling comes from BC and if they dont match up my data gather from another program doesnt work as it needs both pieces of information to work.

**Steffen Thomsen** · 07-04-2012, 03:11 PM

The sort method wont work with the current datastructure.

Whats the purpose of the workbook? Just thinking there might be a better way of structuring the workbook.

**joshnathan** · 07-04-2012, 03:47 PM

Hmm can't really give away the exact details but its basically like this:
Assume Col C: names of people Col D: there age (this is being imported so its always changed might be more names added etc) Col E = the number of people with the same birthday - this is being done with an importation formula for all 3 cols
i want to find people with ages that are greater then x and less then Y. And only display those who meet the criteria, however if the list updates and more people with the correct criteria are added they should be added to the list too. I then need to take an average of the volume of people with the same birthday.

If this isn't possible. I'd like to create a seperate list next to the list of Birthdays and names, of the peoples names and birthdays of those who meet the age range. Then from here i can get the volumes for only these people and then compute the average.

Hope this makes sense.

**Steffen Thomsen** · 07-04-2012, 04:03 PM

What is the criterie, you can build this into the formula and in this way eliminate the need for duplicate data

**joshnathan** · 07-04-2012, 04:09 PM

At the moment its
=IF(AND($D26 >= $C$2, $D26 <= $D$2), $D26, "") So C2 is the lowest level and D2 is the highest these are dynamic and update based on the figures in it.

**Steffen Thomsen** · 07-05-2012, 02:25 AM

Hi,

Try looking at this workbook.
I have removed the formulas in columns H and I and added an event.
Every time a value is changed, added or removed from columns C and D it checks if it falls between the criteria if i does i copies it to Columns H and I.

Try and play around with it, changing values and adding new data and see if it behaves the way you want.