Multi Condition Report

[niting]

Hi forum,


I have a sheet with colA with job codes and COlB with vendor name. Other information are in other columns , specifically , like amount in ColL. The entry for one particular vendor against one particular job code appears many number of times in the sheet.


Now, in other sheet, i wish to create 2 reports based on the 1st sheet.


One with the job codes as primary key and the vendor's name and total amount for one particular vendor against that job code. the problem I have is, do I have to manually pick up the names of the vendors against the job code from the 1st sheet or is there a formula wherein it will pick only unique name, since the same name appears many number of times in 1st sheet.


Secondly, the report with the vendor names as primary key and the job code and total amount for a particular job code. The problem here is the same as mentioned above. since the job code appears multiple times.


Sumifs will help to retrieve the total amount. however, vlookups or other look ups won't pick the unique values


I hope I am able to explain my problem.


Thanks

Niting

 

[Luke M]

Both reports sound like they could be quickly generated using a PivotTable. Simply select your data table in sheet1, Data - PivotTable create. Then you'll put the job code/vendor name in the row area, and the total amount (summed) in the data area.


This lets you quickly generate unique names AND gather the sums.

 

[dan_l]

Yeah. This is what pivot tables are great at.


If the data is subject to change and/or addition, you can just set the pivot to look at a dynamic range. a couple of really simple lines of vba and you've got yourself a pretty nifty tool.