Sorting for Specific words/ numbers from list

[Heyna]

Hi Folks;

I am trying to figure out a way to sort some information from data tha
appears in a worksheet via an import. The import works like
straightforward paste function from another program (in this case
Quickbooks). The information pastes the same every time, i.e. dat
appears in the same columns every time. These columns have a name in
column (ex. A1)and a monetary value in another column (ex. D1). Wha
I'm having to try and do is make a formula that will sort similar name
and sum up the monetary values that match up with them. The "simila
names" are actually numeric codes (ex. 1200, 1300, 1400). The big catc
is that, due to the way this program "takes over" with the import/paste
the codes do not land in a cell alone. It also pastes along with th
code a description. The descriptions can vary (ex. one can sa
"1200-labor" the other "1200-materials"). My question is: can you do
sort that asks to look at a list of data and have it count only cell
that INCLUDE a "similar name" (ex. 1200-hammer is included, 1300-pi
not included, 1200-nail included), and have it sum the amounts?
My thanks in advance, and please let me know if I'm not being clea
enough.
-Scot

 

[Rita Palazzi]

=SUMIF(a5:a10,"*TEXT*",b5:b10)

a5-a10 is text field to test against
b5-b10 is value to add if true

So if you'd put =sumif(a5:a10,"1200*",b5:b10)

you'd sum all with 1200 at beginning field.

Hope this helps!
Rita Palazzi
Senior Engineer / Global Trade Services
FedEx Express

 

[Ron Rosenfeld]

Hi Folks;

I am trying to figure out a way to sort some information from data that
appears in a worksheet via an import. The import works like a
straightforward paste function from another program (in this case,
Quickbooks). The information pastes the same every time, i.e. data
appears in the same columns every time. These columns have a name in a
column (ex. A1)and a monetary value in another column (ex. D1). What
I'm having to try and do is make a formula that will sort similar names
and sum up the monetary values that match up with them. The "similar
names" are actually numeric codes (ex. 1200, 1300, 1400). The big catch
is that, due to the way this program "takes over" with the import/paste,
the codes do not land in a cell alone. It also pastes along with the
code a description. The descriptions can vary (ex. one can say
"1200-labor" the other "1200-materials"). My question is: can you do a
sort that asks to look at a list of data and have it count only cells
that INCLUDE a "similar name" (ex. 1200-hammer is included, 1300-pin
not included, 1200-nail included), and have it sum the amounts?
My thanks in advance, and please let me know if I'm not being clear
enough.
-Scott

If the account numbers are all followed by text, you may use the formula:

=SUMIF(A1:A100,"1200*",D1100)

If the 1200 is in some cell named "acct_num", then:

=SUMIF(A1:A100,"="&acct_num&"*", D1100)

If it may be the case that the 1200 is sometimes a number (no text added on),
then:

=SUMIF(A1:A100,"="&acct_num&"*", D1100) + =SUMIF(A1:A100,acct_num, D1100)


--ron

 

[Leigh Ann]

If the data that is pasted, such as 1200-tool or 1300-hardware, always starts
with the same number of digits, you could copy that entire column into
another column and use the "Text to Columns" function under "Data." Use the
"fixed width" method and separate your data. You will want to insert a few
additional blank columns prior to separating your data so that data you need
is not written over. If the length of the number varies, use the "Text to
Columns" function but use "deliminated" and choose the "other" option and put
a dash in the box so that Excel will separate your data at the dash.