consolidate information

[SQUIDD]

Hi everyone

I have a load of data in a column like this

cell a1 20
cell a5 44
cell a12 96

and so on, but how would i go about copying the data into say column b so that there are no gaps between data like this.

b1 would be 20
b2 would be 44
b3 would be 96
and so on.

hopefully there is a function to do it rather than code
thanks for your help.

Dave

 

[adept]

Is the data in column a connected to other data?

If not highlight the column and select Data in the menu bar, and sort the data.

Hope that helps.

Craig

 

[SQUIDD]

thanks for you help but i dont think sort will work because the data goes up and down.

3

2.5

1

6

2

9

something like this but i need it to stay in the correct order.
any other ideas would be appriciated.

Dave

 

[adept]

I use this macro all the time.

Just make sure that you don't have any other data in other columns because it will be DELETED!!!

Test this on a sample bit of data not your working copy!

Sub Delete_Blank_Rows()
' This macro deletes all blank rows on the active worksheet

 Dim rng As Range, cell As Range, del As Range
 Set rng = Intersect(Range("E:E"), ActiveSheet.UsedRange)
 For Each cell In rng
   If (cell.Value) = "" Then
       If del Is Nothing Then
          Set del = cell
       Else: Set del = Union(del, cell)
       End If
    End If
 Next cell
 On Error Resume Next
 del.EntireRow.Delete
End Sub

Change the Set rng = Intersect(Range("E:E"), line to the column that you have the data in.

Run it from Tools>Macros>Macros>Delete_Blank_Rows

Hope that helps.

Craig

 

[SQUIDD]

thankyou very much, it worked great.I will keep that 1 as it is a very useful tool.

Dave

 

[Jacophile]

Hi, the other way is to use an array formula like this:

{=INDEX(List,SMALL(IF(NOT(List=""),ROW(List)-ROW(HeadofList)+1),ROW()-ROW(headofconsList)+1))}

where List is the raw data, HeadofList is the first entry in it and HeadofCons list is the first cell in your consolidated list. You just paste the above formula, confirm with control-shift-enter and fill down as far as you need.