PDA

View Full Version : Solved: Script to add values in new sheet without blank rows



Skopweb
08-19-2009, 12:00 AM
Hello All
I'm looking if any script can help me to do the following task
Data is present in Sheet 1 under columns A,B,C
Column A contains data of only two categories, Apple, Oranges
The price value and quantity of the respective categories in B and C

In a Sheet2 i need the script to look for Apples and display their corresponding values present in B and C
The data should be appended in simulataneous manner (no blanks between rows)

In Sheet 3 the same should occur for Oranges

Using Excel formulas will give me the values but it will leave blanks or something else if the value in not found in the respective row.
I need to avoid those invalid or Blank rows...


Please assist
Skopweb

lucas
08-19-2009, 09:37 AM
Moved to appropriate forum. Please post Excel questions in the Excel help forum.

Bob Phillips
08-19-2009, 10:12 AM
Try this array formula

=IF(ISERROR(SMALL(IF(Sheet1!$A$1:$A$20="Apples",ROW(Sheet1!$A$1:$A$20),""),ROW(A1))),"",
INDEX(Sheet1!B$1:B$20,SMALL(IF(Sheet1!$A$1:$A$20="Apples",ROW(Sheet1!$A$1:$A$20),""),ROW(A1))))

copy down and acroos and adjust for sheet3 Oranges

Skopweb
08-20-2009, 12:14 AM
thanks lucas for moving the forum to the appropirate. BTW this is my first post so was not aware of the path..
:peace:

Skopweb
08-20-2009, 12:17 AM
Thanks XLD
But it only gives the first value for Apples and not for the remaining .
Is there a way to get it done via a script...

:)

Bob Phillips
08-20-2009, 01:00 AM
Copy it down and across and mentioned, and it is an ARRAY formula, so Ctrl-Shift-Enter.

Skopweb
08-20-2009, 11:26 PM
XLD
please refer to the attached file.... Let me know if i'm doing anything incorrect

Bob Phillips
08-21-2009, 12:45 AM
Yes, you did not array enter it - Ctrl-Shift-Enter.

If you copy it across to column B and down you will see the rest.

Skopweb
08-21-2009, 01:53 AM
XLD
Thanks ... this seems to be working now ...... i was not able to understand array initially .....

:) :beerchug: :)