insert new row into sorted data

[irato2]

Hi, i am struggling to figure out how to set up a workbook which will d
this; I want a worksheet which will hold unsorted rows of data in
database-like format. I also want another worksheet which will hold th
sorted data, but in a format slightly more apealling than just a sorte
database. I want the sorted data to be seperated( by empty rows) int
related rows, e.g. all rows with the same data in column 1 and 2 to b
grouped then a gap and so on.

How I wanted to do this was start it off manually using autofilter an
cut-and-paste to set out the format in the 'sorted' worksheet then hav
a macro/event procedure which, whenever someone adds a new row to th
'unsorted' sheet, will find the appropriate place in the 'sorted' shee
and insert a copy of the row in there.
this would hopefully allow me to set up a sum function in one colum
which will sum all the data for each group of sorted rows.

How I do this I have no idea, im quite new to Excel and vba, but i
would be a very useful feature. Ive attached a spreadsheet with a smal
sample of the data and how I wanted the data organised.
Ive tried thinking about it using match or lookup but The data i
needed to be sorted on the first 3 columns and so I couldnt figure ou
how to appy them, maybe nest them? i dont know

Anyway if anyone has any ideas or has done this before please give me
reply.

thanks, Stuar

Attachment filename: sortsample.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=52036

 

[Debra Dalgleish]

You could create a pivot table from the data, with Site, Var, Date and
Shift in the Row area, and Volume and Machine Hrs in the Data area.

There are pivot table instructions and links here:

http://www.peltiertech.com/Excel/Pivots/pivotstart.htm

 

[irato]

Id prefer to avoid using a pivot table because it would probably affect
my future plans for the spreadsheet.

Is it posible to insert new rows from the 'unsorted' sheet into their
proper place in the 'sorted' sheet using an excel function like vlookup
or even a combination of functions?. Bearing in mind that the data is
supposed to be sorted on the first 3 columns.

 

[AlfD]

Hi!

You will achieve the layout you are looking for if you do the
following:

1) Make a copy of the main database on another worksheet.

2) With this worksheet: sort by colA/ColB/ColC

3) Use data < Subtotals choosing changes in ColA (Site), sum of ColE
(Volume).

4) If it feels clumsy to leave Bloggs Total in the line between blocks,
you might want to reduce it to Bloggs (Find Total and replace with "")
or to empty the row completely.

All of these steps could be put into a macro by recording.

Alf

 

[irato2]

thanks Alfd, thats alot smarter than my idea . Im not worried abou
the clearing '[site] total' cells, but it would be cool if I could ad
to the macro which ,after sorting, goes through column A and an
whenever it sees a cell with 'site total' it inserts a couple of row
after that row. I can figure out most of this except for the functio
that checks whether a cell contains 'site total'

Im looking for a something like a fuction which will search a cell fo
the string "total" and return true if it finds it. Would the functio
-FIND- work? it says it returns the index of the the string if i
finds it, what does it return if it doesnt find the sub-string?

or could I just do this?

if(activecell.value = "*total") then insert row

and just replace * with whichever wildcard vba uses (whatever that is)

 

[AlfD]

Hi!

Sounds like progress.

A couple of possibilities.

1. if(right(activecell.value,5) = "Total") will pick out cells wher
the last 5 characters spell out Total.

2. Alternatively: why fight it: col B is empty at all the crucia
points. So why not just test that?


Al

 

[irato2]

thanks alfd, ill give one of those a go. Thats all my problems solved..