Copy selective data

I want the information from one workbook, to transfer in other workbook according to the products ordered. For ex. anytime that there is computer, copy the corresponding data such as cost per unit, units ordered,tax etc. in another worksheet. (If this is confusing please see the attachment)

Something like this:

CHeck this out:

You can solve it with Pivot Table...

(and I'll need more time for explanation for formula)

I have a dept from yesterday...

So... Main idea is from list of products which can repeat create unique values (adding +1 for each time that value repeats).
That's why I need helper column A in Sheet1.
=COUNTIF($D$6:D6;D6)

This formula will count first a "computer" in range D6:D6 (result =1)
Then you go down:
=COUNTIF($D$6:D7;D7)
This formula will count "scaner" in range D6:D7 (result =1)
etc.

Then you'll have computer again:
=COUNTIF($D$6:D10;D10)
Where you have 2 computers in range D6:D10 so result is 2.... Next time computer appear it will return 3 etc.

Joining D and A column you'll get unique list now: computer1, scaner1, printer1, calculator1, computer2, scaner2, computer3 etc...

This will became a new array (A second INDEX formula) where you'll match your values because if you don't do that when you MATCH computer it will always return first value.

Now, when you have this additional column we can go further:

=INDEX(Sheet1!B$6:B$24; MATCH($B$1 & ROWS($B$4:B4); INDEX(Sheet1!$D$6:$D$24& Sheet1!$A$6:$A$24; 0); 0));"")

If you don't know how INDEX formula work check a help for explanation and here is just a brief:

INDEX(array,row_num,column_num)

In our example:
- array will be column of Consumers
- row_number will be match of unique values explained above
- column_num will be 0 (no columns, just rows)

For return Consumer 2 you have:

=INDEX(Sheet1!B$6:B$24, 2, 0)

and number 2 you get when you MATCH "scaner1" in your new array.

Your new array is INDEX(Sheet1!$D$6:$D$24& Sheet1!$A$6:$A$24; 0)

(note: If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively.)

and you want to match $B$1 & ROWS($B$4:B4)

where B1 is always same value (choose from drop down menu) and ROWS formula will increase by 1 in each row.
So, if "scaner" is in B1 in combination with & ROWS($B$4:B4) you'll get:
scaner1, scaner2, scaner3, scaner4, scaner5, scaner6 etc.

Now you can MATCH each scanerX with unique list (again, explained above)...

For other columns is same formula, you only change array you want return value from:
i.e.
=INDEX(Sheet1!C$6:C$24; MATCH($B$1 & ROWS($B$4:B4); INDEX(Sheet1!$D$6:$D$24& Sheet1!$A$6:$A$24; 0); 0)))

and everything else is the same.

Finally, to create empty values you can use:

=IF(COUNTIF(Sheet1!$D$6:$D$24;$B$1)>=ROWS($B$4:B4); formula; "")

Countif of scaners is 5.
So no point of looking below value scaner5. If 6 is reached leave empty.

Thank you very much it helped me a lot.