• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting data based on complex criteria

JSH720

New Member
I have a list in excel. Items can have several bids on them (the item number is unique, but appears as many times as there are bids on it). I need to extract from the data,


for each unique item number that has a bid 1 and a bid 2, copy the row of data for each bid and extract to another report. Then subtract bid 2 from bid 1, which will give you the $$ left on the table.


I'll end up with only those item #s that have 2 bids on them and the data associated with them and the $$ left on the table.


Help???!!
 
Here you go...


First create a helper column, where we show the count of bids upto that point against the item in that line. Assuming item number is in column B, the helper column can have a countif formula like this: =countif($B$1:B1,B1), copy paste the formula over the range. Lets call this column Bid number.


Also, add one more column and display the total bids the item had. The formula is similar, =countif($B$1:$B$100,B1), call this column as Bid count.


now, create a pivot table with the whole range including the helper columns


Select items as the row label

select bid count as report filter, select only values where bidcount = 2

Select bid number as column label

Select amount as the values.

Now right click on the amount values in pivot and go to field properties.

Go to "Show value as" tab

Select "Difference from" option

Select "Bid" as base field

Select "1" as base item


That is all.. now second column in the pivot shows the differences
 
Back
Top