• 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.

Help with excel formula/filtering

S

smmatt

New Member
I'm working with a very large data set that I've imported from a CSV file. The data is sales records from one of our amazon store. What I need is a filter or formula to quickly parse the data so I can exclude any sales that do not have a sales tax data. Normally this would be easy but all of the data with a dollar value is in the same column and there are multiple rows of data for each individual "order id" (a single item/product).

[pre]
Code: 
31-Jan-12	103-4669758-3621868	1UD3TF0002BU	Order Payment	Amazon fees	Commission	($1.77)		Forts Henry and Donelson--The Key to the Confederate Heartland
31-Jan-12	103-4669758-3621868	1UD3TF0002BU	Order Payment	Amazon fees	Variable closing fee	($1.35)		Forts Henry and Donelson--The Key to the Confederate Heartland
31-Jan-12	103-4669758-3621868	1UD3TF0002BU	Order Payment	Product charges		$11.80 	1	Forts Henry and Donelson--The Key to the Confederate Heartland
31-Jan-12	103-4669758-3621868	1UD3TF0002BU	Order Payment	Other	Shipping	$3.99 		Forts Henry and Donelson--The Key to the Confederate Heartland
31-Jan-12	105-3811971-2984218	1UD3TF000294	Order Payment	Amazon fees	Commission	($1.13)		Display Postscript Programming
31-Jan-12	105-3811971-2984218	1UD3TF000294	Order Payment	Amazon fees	Variable closing fee	($1.35)		Display Postscript Programming
31-Jan-12	105-3811971-2984218	1UD3TF000294	Order Payment	Product charges		$7.50 	1	Display Postscript Programming
31-Jan-12	105-3811971-2984218	1UD3TF000294	Order Payment	Other	Product Tax	$0.58 		Display Postscript Programming
31-Jan-12	105-3811971-2984218	1UD3TF000294	Order Payment	Other	Shipping	$3.99 		Display Postscript Programming
[/pre]

The most concise way I can describe what I'm trying to do is to filter out the entire row of data for any given order IDs (column B) for which there is no Product Tax entry (column F).


Here is the entire .xls:


http://dl.dropbox.com/u/30006457/report1.xlsx
 
Smmat


Firstly, Welcome to the Chandoo.org Forums


Why not convert it to a Table,

select a cell in the data, any cell, goto the Insert, Table

You can now use the drop down next to each headers to select various entries or exclude entries
 
Thanks for the welcome, Hui.


I tried converting it to a table as you suggested but that seems very similar to the sort & filter option on the home tab (excel 2007) which could not quite get me the data I needed. The problem with doing it that way is that this will get me the "product tax" rows on their own but I also need the "product charges" row which correspond with the tax values and I need the "product charges" values which do not have a tax entry to be excluded.


The only way I can think to do it would be to have a filter check if the value "product tax" is present in column F and then check column B and filter out any rows that do not have an "order id" value that matches. However, that seems incredibly complicated/contrived to me and I have absolutely no idea how I'd even begin to start writing that filter.
 
You can apply multiple filters progressively as you described and the data is live as you apply the filters


You could also setup an Advanced Filter which allows multiple criteria to be applied on a batch style basis


Debra Daglish at Contextures Blog has some great posts about Advanced Filters

Start here: http://www.contextures.com/xladvfilter01.html


Advanced filters can do simple or very very complex filters
 
Hi, smmatt!


Sorry for arriving at dessert time, maybe you can use helper columns?


If so, in J5 type:

=SI($F5="Product Tax";B5;"") -----> in english: =IF($F5="Product Tax",B5,"")

In K5 type:

=SI(NO(ESNOD(BUSCARV(B5;J:J;1;FALSO)));"With tax";"") -----> in english: =IF(NOT(ISNA(VLOOKUP(B5,J:J,1,FALSE))),"With tax","")

And then filter by K column.


Regards!


EDIT: simplyfing...

=IF(ISNA(VLOOKUP(B5,J:J,1,FALSE)),"","With tax")
 
Ahah! The helper columns have done the trick. After filtering K for the "with tax" value I was able to add two more helper columns to L & M, =IF(E33="product charges", G33," ") and =IF(F33="product tax", G33, " ") respectively, to achieve the results I was looking for.


Thanks for all the help guys.
 
Hi, smmatt!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top