Copy Data from One sheet to other in same excel with some conditions.

Hi All,

I am bit new to excel programming and Stuff. I am facing some issue in a Task Can you please help

Data:

There are two sheets : Sheet1 is Input and Sheet 2 is Output.

Sheet 1 has following columns.

Item Shop Buyer Quantity in Jan Quantity in Feb

Requirement:

Whenever I enter "Quantity in Jan" for a particular buyer , sum of all Quantity for a particular buyer should be displayed in Sheet 2 for that particular buyer name.Here the buyer names will be fixed in both sheets but I will be adding details in sheet 1 for buyers and this should reflect in sheet 2.

Example: In sheet 1 the data is as follows


Item Shop Buyer Quantity in Jan Quantity in Feb
Car Shop1 Tom 1 7
Cycle Shop2 Nick 1 6
Car Shop1 Tom 3 2


Then in sheet 2, following data should be displayed automatically


Tom Nick
Quantity in Jan 4 1
Quantity in Feb 9 6

Pl see the attached file.

Thanks Mr. Srinivasa Murthy.

But I faced faced the following error, can you please look into it.

1.When I add a new row for Tom and enter values in Input sheet. These values are not displayed in output sheet

2. If I add another column "Quantity in Mar". Sheet does not display the results.


Can you please provide solution for this.


Attached is the file "Demo ans_doubts"

Hi Taj48,

I modified the amazing formula kvsrinivasamurthy provided to work with dynamic ranges.

I set the following dynamic ranges

Formula:

Data=OFFSET(Input!$D$2,0,0,COUNTA(Input!$A:$A)-1,COUNTA(Input!$1:$1)-3)

Headers=OFFSET(Input!$D$1,0,0,1,COUNTA(Input!$1:$1)-3)

Names=OFFSET(Input!$C$2,0,0,COUNTA(Input!$C:$C)-1,1)

So the modified formula looks like this in "B2"

Formula:

=SUMPRODUCT(INDEX(Data,,MATCH(Output!$A2,Headers,0)),--(Names=Output!B$1))

I have attached the workbook.

Attachment 263621

Thanks

Awesome fredlo 2010,

IT Works !

I have a new requirement Can you please help me.

In my sheet if I filter by a Shop for example "Shop 1" . I get the following data

Shop Vehicle Buyer Quantity in Jan Quantity in Feb
Shop1 Car Jim 1 5 5
Shop1 Car Nick 5 6 9
Shop1 Scooter Jim 7 8 0
Shop1 Cycle Mary 9 0 0

Now I need the following :

1.Number of distinct vehicles purchased from "Shop 1" and place it in the Sheet "Distinct".
2.Sum of "Quantity in Jan" in sheet "Input" for all vehicles purchased from Shop1 and place in sheet distinct.

Following is the example values in Sheet "Distinct"


Shop Number of Distinct Vehicles Total Quantity in Jan
Shop1 3 22
Shop2
Shop3

Also Attached is the sheet.

Thanks.

Hi Taj48,

I was looking at your new requirement and I don't know how to solve the issue. Lets wait and someone with more experience might be able to help.

Also Have you tried using Pivot tables? You can create amazing reports with them.

Thanks

Actually, the report here is dynamically generating and I have never used pivot tables. Can you please check it and let me know.

Thanks in Advance.