Stupid excel question

[Larry Robinson]

Okay folks... I'm an idiot. Here goes: is there a way to "automate" excel
in order to pull figures from one spreadsheet and place them onto another
spreadsheet. For instance, let's say I have worksheet entitled "list" with
30 items listed in in column A - the item "banana" is listed 4 times in
column A. In column B there are numbers that correspond to each item in
column A.

On another worksheet I'll call "Totals", I would like to pull all of the
numbers associated with items from "list" in column A called "banana" and
total them.

I hope I am illustrating the example clearly. I could always take a
calculator and add up all of the numbers associated to the item "banana" and
then enter that number on the other worksheet but I'm lazy. Also, every
month the "items" in column A change order and one month there are 30 items,
the next month there are 45 items.

I guess what I'm asking is, "does excel have a "find" function that can
total fields associated like words". Hmmmmmm... Any thoughts? Thanks!

 

[JE McGimpsey]

Okay folks... I'm an idiot. Here goes: is there a way to "automate" excel
in order to pull figures from one spreadsheet and place them onto another
spreadsheet. For instance, let's say I have worksheet entitled "list" with
30 items listed in in column A - the item "banana" is listed 4 times in
column A. In column B there are numbers that correspond to each item in
column A.

On another worksheet I'll call "Totals", I would like to pull all of the
numbers associated with items from "list" in column A called "banana" and
total them.

One way:

On your "Totals" sheet:

=SUMIF(list!A:A, "banana", list!B:B)

 

[Larry Robinson]

JE: thank you so much for your response. One more question: is it
possible to import a broad range of data from one worksheet (List) to
another worksheet (totals) by using the cursor to drag across many columns
and rows - to just drop all of that data?

Hope I'm making myself clear. Thank you in advance.

 

[Larry Robinson]

JE: never mind... My last post was indeed very stupid. Sorry!

 

[Larry Robinson]

JE: thanks so much for your help in advance. Okay, another question based
on the hypothetical below...

I have a list of various items: banana, apple, orange, peach. I have
multiple rows which contain the designation banana. On another worksheet,
could I have excel find all of the rows that say "banana" in column "A" and
pull all of the data from the those rows in columns E,F,G,H,I,J,K,L, and
place this data in multiple rows?

Is that possible? Do you have a magic formula? Thanks! larry

 

[Domenic]

Assumptions:

Sheet1!A1:L10 contains your source data

Sheet2!A1 contains your your fruit of interest, such as Banana

Formulas:

Sheet2!B1, copied down:

=IF(ROWS(B$1:B1)<=COUNTIF(Sheet1!$A$1:$A$10,$A$1),INDEX(Sheet1!A$1:A$10,S
MALL(IF(Sheet1!$A$1:$A$10=$A$1,ROW(Sheet1!$A$1:$A$10)-ROW(Sheet1!$A$1)+1)
,ROWS(B$1:B1))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Sheet2!C1, copied down and across:

=IF(ROWS(C$1:C1)<=COUNTIF(Sheet1!$A$1:$A$10,$A$1),INDEX(Sheet1!E$1:E$10,S
MALL(IF(Sheet1!$A$1:$A$10=$A$1,ROW(Sheet1!$A$1:$A$10)-ROW(Sheet1!$A$1)+1)
,ROWS(C$1:C1))),"")

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

 

[Barry Wainwright [MVP]]

I have a list of various items: banana, apple, orange, peach. I have
multiple rows which contain the designation banana. On another worksheet,
could I have excel find all of the rows that say "banana" in column "A" and
pull all of the data from the those rows in columns E,F,G,H,I,J,K,L, and
place this data in multiple rows?

Is that possible? Do you have a magic formula? Thanks! larry

Possible, and easy.

Read up on 'pivot tables' in the help and at these web pages:
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.cpearson.com/excel/pivots.htm
http://www.microsoft.com/BusinessSolutions/excel_pivot_tables_collins.mspx
http://techrepublic.com.com/5100-1035_11-1034640.html#

 

[JE McGimpsey]

JE: thanks so much for your help in advance. Okay, another question based
on the hypothetical below...

I have a list of various items: banana, apple, orange, peach. I have
multiple rows which contain the designation banana. On another worksheet,
could I have excel find all of the rows that say "banana" in column "A" and
pull all of the data from the those rows in columns E,F,G,H,I,J,K,L, and
place this data in multiple rows?

Is that possible? Do you have a magic formula? Thanks! larry

As Barry wrote, a Pivot Table seems tailor made for your application.