move rows of data seperated in a sheet to a sheet with no separat

[Guest]

I want some scattered rows of data that are uniquely identified by a number
(example 3), in a cell in their row. Using an IF function, I want to move
the data to another page, then have it all move up to fill the first open
row. Is this possible. I know how to get the IF part done. But not the
close up the spaces part. For example

Journal entry page
date vendor item amount code

11/30 progress elec 75 .00 3
12/1 taylors supplies 32.90 2
12/29 progress elec 44.00 3


Progress account page (this is how it brings it now, with a space)

Date Item Amt
11/30 elec 75.00
I want to eliminate this space and have the data move up automatically.
12/29 elec 44.00

Is this possible?

 

[Roger Govier]

Hi Lynn

One way
Select columns A:C
Data>Sort>ColumnA>Ascending
All the blank rows will "fall" to the bottom of the list.

 

[Guest]

If you want it dynamic,
here's a non-array formulas play which delivers ..

A sample construct is available at:
http://www.savefile.com/files/351776
AutoCopy Lines by Code col in New Sht.xls

Assume source data in sheet: Journal entry,
cols A to E, where the key col E = code,
data from row2 down

In sheet: Progress account,
Assume the desired code will be entered in A1, eg: 3
Paste the col labels: date, vendor, item, amt into C1:F1

Put in B2:
=IF($A$1="","",IF('Journal entry'!E2=$A$1,ROW(),""))
(Leave B1 blank)

Put in C2:
=IF(ROW(A1)>COUNT($B:$B),"",INDEX('Journal entry'!A:A,SMALL($B:$B,ROW(A1))))

Copy C2 to F2. Select B2:F2, copy down to cover the max expected extent of
data in "Journal entry", say down to F100. Format col C as date to taste.
Hide away cols B & D as desired. Cols C, E and F will return the required
results, with all lines neatly bunched at the top.

 

[Max]

Received email reply from OP:

Dear Max,

thank you for the help on the consolidation of rows, on my question
of Dec. 19. It works perfectly. I am very grateful.

Lynn

---