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?

Hi Lynn

One way
Select columns A:C
DataSortColumnAAscending
All the blank rows will "fall" to the bottom of the list.
--
Regards

Roger Govier


"Lynn" <Lynn @discussions.microsoft.com wrote in message
...
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?

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lynn" wrote:
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?

Let's say:
Your header in Sheet1 row 1
and your data in A2 to A100

In sheet2:
A2 for date
=IF(ISERR(SMALL(IF(Sheet1!$E$2:$E$100=3,ROW(INDIRE CT("1:"&ROWS(Sheet1!$E$2:$E$100)))),ROWS($1:1)))," ",INDEX(Sheet1!$A$2:$A$100,SMALL(IF(Sheet1!$E$2:$E $100=3,ROW(INDIRECT("1:"&ROWS(Sheet1!$E$2:$E$100)) )),ROWS($1:1))))
ctrl+shift+enter (not just enter)

B2 for Item
=IF(ISERR(SMALL(IF(Sheet1!$E$2:$E$100=3,ROW(INDIRE CT("1:"&ROWS(Sheet1!$E$2:$E$100)))),ROWS($1:1)))," ",INDEX(Sheet1!$C$2:$C$100,SMALL(IF(Sheet1!$E$2:$E $100=3,ROW(INDIRECT("1:"&ROWS(Sheet1!$E$2:$E$100)) )),ROWS($1:1))))
ctrl+shift+enter (not just enter)

C2 for Amount
=IF(ISERR(SMALL(IF(Sheet1!$E$2:$E$100=3,ROW(INDIRE CT("1:"&ROWS(Sheet1!$E$2:$E$100)))),ROWS($1:1)))," ",INDEX(Sheet1!$D$2:$D$100,SMALL(IF(Sheet1!$E$2:$E $100=3,ROW(INDIRECT("1:"&ROWS(Sheet1!$E$2:$E$100)) )),ROWS($1:1))))
ctrl+shift+enter (not just enter)
Select range from A2:C2 then copy down as far as needed






"Lynn" wrote:

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?

without farther formula

at the Journal entry page, select column header with "code",
on menu
DataFilterAutofilter [choose the "code" number (e.g. 3)]
select all the cells from the corner header [left of A header]
right click mouse copy
go to next "page" on A1.
right click mouse paste
remove columns you dont need.

you may need dynamic formula inside the progress account page if you requires daily updates for presentation, yet, you have to be careful not to insert any row of entry in between the previously established Journal entry page.

Another column with [ifs] series lookup code may be needed within the "Journal entry page" .

happy holidays hohoho
driller2
:)

Received email reply from OP:

--- Lynn wrote:
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

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---