sorting and adding cells in different work sheet? (hard stuff)

[doyree]

hello
i have another question to ask to this great forum!!
this place is great!!

here is my question.

i got a worksheet called "ABC", "DEF" and "LIST"
i have datas stored in ABC and DEF, want them transfered to LIST
i can just simply do "='ABC'!A1" on "LIST" page then just drag all, same
thing for worksheet "DEF"
but i'm too lazy to do that.. or least my boss is... with certain conditions.

here's the condition;

1. ABC and DEF contains datas from formula. some cells are just marked with
"0" due to the formula result
i want it to copy all cells (cell values only) from certain columns from
"ABC", sort itself OR leave out cells that has "0" value,

2. then, same thing for "DEF", add "DEF" data on "LIST" where "ABC" data had
left off.

is this possible for excel?

here's the sample that i would want it to look like;

WORKSHEET"ABC" WORKSHEET "DEF"
A B A B
abcd 1234 ZYX 1098
efgh 5678 0 0
0 0 WVU 7654
ijkl 9101 TSR 3210


WORKSHEET "LIST"

abcd 1234 (start data from worksheet "ABC")
efgh 5678
ijkl 9101
ZYX 1098 (start data from worksheet "DEF" where "ABC" had left off")
WVU 7654
TSR 3210

i hope excel can do this, if not.. oh well..
please help me!!! thanks!!!

 

[Pete_UK]

Apply a custom filter to column B of ABC sheet - select Does Not Equal
in one panel and enter 0 (zero) in the other and click OK. Then
highlight all the visible data in columns A and B, click <copy>, click
on the LIST sheet tab, and on cell A1 and press <Enter>. This will
have copied the first part of your list. Press <End> once followed by
<down-arrow> to position your cursor in the next available cell in
column A.

Then click onto DEF sheet, apply autofilter to column B as before -
Custom | Does Not Equal | zero, then highlight all the visible cells
in column A and B, click <copy>, click on the LIST sheet and press
<Enter>.

That should give you what you want.

Hope this helps.

Pete

 

[Herbert Seidenberg]

Excel 2003
For the really lazy...
http://www.freefilehosting.net/download/3c05f

 

[doyree]

thank you much!!

looks like pivot consolidate did the magic.
only problem is that i would have to "refresh" everytime i add data... what
a work!!!
j/k

anyways, got another question.

i was just trying to use consolidate range myself on different worksheet.

worksheet 1.
A B C
name name2 name3
A Z1 A1
B Z2 B2
C Z3 C3

worksheet 2.
A B C
name name2 name3
I Z9 I9
J Z10 J10
K Z11 K11
L Z12 L12

when i did consolidate range, i got something like this;





Count of Value Column
Row name 3 name 2 Grand Total
A 1 1 2
B 1 1 2
C 1 1 2
I 1 1 2
J 1 1 2
K 1 1 2
Grand Total 6 6 12

what must i do to have name 3 and name 2 info appear?
also, name 2 and name 3's position has been switched around.
what must i do to correct this?

please help
thank you!!!!

 

[Herbert Seidenberg]

Pivot Table is designed for numbers, not text.
The first row and column are text headers,
the rest should be numbers.
However, I wrote a program that translates
the 1's that you got into text.
The macro behind the "Translate" button,
is tailored for my layout and probably will not
work in a new spreadsheet without modifications.
http://www.freefilehosting.net/download/3c0b4

 

[doyree]

ah!! is that why it wasn't working because it was in text format?
i need to have it understand and accept as it is text as you programmed eh?
thanks!!!!!! bunch!!!!!!!!!!!!!!!!!

 

[doyree]

i was looking at your first download file....

when i got formulas answered from other users, i could figure out and
understand the formula with help from "help" in excel....
but yours.. man.....

i feel like i got F* lot more way to go...

what the heck is
IF(RANDBETWEEN(0,1),CHAR(RANDBETWEEN(65,85))&INDEX(vowel,RANDBETWEEN(1,5))&CHAR(RANDBETWEEN(97,117)),0)????

it's..
1. IF(RANDBETWEEN(0,1),
if randbetween (0,1), (meaning 0 or 1)
then return value

2.
CHAR(RANDBETWEEN(65,85))&INDEX(vowel,RANDBETWEEN(1,5))&CHAR(RANDBETWEEN(97,117))
why CHAR Between 65~85?
and INDEX is, (range,y,x) but you have "vowel" for range then 1~4?
then another digit between 97~117...
if not, then return value "0"

i got upto 1, but what's the deal with 2?

if u can explain how it works, i would most appreciate!
will treat you for mcdonalds!!!!

thanks anyways!!!

 

[doyree]

i give up...

i'm going to just let him do copy paste himself or do it for him myself!!
lol

thanks

 

[Herbert Seidenberg]

The formulas with RAND in them generate
RANDOM data for test purposes.
You can convert them to fixed values with
Copy > Paste Special > Values
or overwrite them with your own data.