Fill certain columns of each row based on data from certain columns in each row

[Trevorm7]

Buyer	Item Name	Quantity	Price	item type	$dog	$cat	#dog	#cat
Joe	dry Cat food	3	$30	123		$30		3
John	wet Cat food	1	$10	321
Bill	Dog food	2	$10	278
Jill	bone for dog	5	$25	432

<tbody>
</tbody>

What I want is something that will automatically fill certain columns based on the item name, quantity and price. For example Joe bought dry cat food. I want something that will look at the item name "dry cat food" in the row and fill the columns $cat with the price and #cat with the quantity. Then fill the item type column based on the item name, for example "dry cat food" is always 123.

In other words I want it to look for all rows with specific item names and fill certain columns with the quantity and price and add the item#. For example something that would do this:
if Item name = a
then copy quantity to column g and h
then copy price to column i and j
then make column e = 12

and so on for many different item names.

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum

Maybe this...

Data Range

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Buyer​	Item Name​	Quantity​	Price​	item type​	$dog​	$cat​	#dog​	#cat​
2​	Joe​	dry Cat food​	3​	$30​	123​		$30​		3​
3​	John​	wet Cat food​	1​	$10​	321​		$10​		1​
4​	Bill​	Dog food​	2​	$10​	278​	$10​		2​
5​	Jill​	bone for dog​	5​	$25​	432​	$25​		5​

<TBODY>
</TBODY>

Formula in F2
=IF(ISNUMBER(SEARCH(SUBSTITUTE(F$1,"$",""),$B2)),$D2,"")
copy across to G2 and down

Formula in H2
=IF(ISNUMBER(SEARCH(SUBSTITUTE(H$1,"#",""),$B2)),$C2,"")
copy across to I2 and down

M.

 

[Trevorm7]

Thanks, but not totally what I need. Let me see if I can explain better.

I have to separate the sales of books, dvds and cds into different statistics, we have them in multiple languages, so it can be "book 1 spanish" "book 1 english" etc. the language doesn't matter, so I'd want it to look for in the item name "book 1" and copy price and quantity data into certain columns based on what I define for that search criteria. I would need to do this to search many different item names, many that would fill the same columns and many that would be different.

1. Search "Hardcover book1" "Hardcover book2" Then copy "price" column to column "g", "h" and "I", copy quantity column to "f". Then I would want it to fill column "e" with a certain number that I specify.

2. Search "Softcover book1" "softcover book2" then do the same thing except instead of column "g", "h" and "I" copy to other specific columns. Then fill column "e" with a different certain number that I specify.

What I need is code with the spots left blank where I can put the specifics. and the ability to put many different item names to search for each 1. and 2..