Lower sales by store

I have an excel sheet where there are many formulas and a column with values and another with the code of the store. With dynamic tables the file is very large (68MB) the book will contain every month and fill up as time passes.

With a formula you could do the same?

They ask me to take the top of the 4 worst sales per store. I include a part of the table with what is currently and Top sheet January where you should see 2 tables of the worst 4 sales of the last 2 months, the same should be done next month.


Thanks for helping

Assuming stores are listed:

in K5

=IFERROR(SMALL(IF($A$2:$A$100=I5,$C$2:$C$100),COUNTIF($I$5:I5,I5)),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

in J5

=IFERROR(INDEX($B$2:$B$100,MATCH(K5,$C$2:$C$100,0)),"")

P.S.: Please change your thread title (or I'll be in trouble!) ... Forum Rule

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Thanks JohnTopley did not understand it first you are looking for sales and then the seller, use it and it works very well
In the same way would be done for the best 4 but using Large.

I have to see if the size is reduced Now File Type: xlsx DATA1SALES.xlsx‎ (16.1 KB) File Type: xlsx DATA1SALES(2).xlsx‎ (13.4 KB) >>>>3.3KB

In "Top January"

in D2

=IFERROR(LARGE(IF(January!$A$2:$A$100=B2,January!$C$2:$C$100),COUNTIF($B$2:B2,B2)),"")

=IFERROR(INDEX(January!$B$2:$B$100,MATCH(D2,January!$C$2:$C$100,0)),"")...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

in C2

=IFERROR(INDEX(January!$B$2:$B$100,MATCH(D3,January!$C$2:$C$100,0)),"")

The company in which I work has several stores identified with codes and requires seeing the 4 lowest sales, which helped me work but in case there are equal values the formula can not find them. Pivot tables in this case have too many MBs.

because you have not placed the original formula in column K !!

Edit Mistakes in final formula. See post #15 below.

... dynamic tables the file is very large (68MB)

This caught my eye.

Just in case array formulas begin to slow the workbook down would you be interested in a (rather lengthy) non array approach?

This solution requires:

1. The source table to be sorted on Cod store.
   (I inserted a sort key in a new column A in case you need to recover the original data order.)
2. A lookup table in K:M that returns the lower and upper row boundaries of each Cod store group.

Copy and paste the Cod stores in column K. Remove duplicates. Then in L2:M8 this formula to set the boundaries.

Formula:

Please Login or Register  to view this content.

Then in H5:I32

Formula:

Please Login or Register  to view this content.

1) Change the PivotTable [Report Layout] to [Show in Tabular Form]
2) Sort the Name column in ascending Sales Order under [More Sort Options]
3) Add in a [Calculated Field] with the formula "=COUNT('Cod Store' )"
4) Click on [Show Values As] and select [Running Total in] with Name as the [Base Field]
5) Filter the Rank column to show only the first 4 items
6) When the database is updated, refresh the PivotTable and do the filtering in Rank column again

Originally Posted by josephteh

1) Change the PivotTable [Report Layout] to [Show in Tabular Form]
2) Sort the Name column in ascending Sales Order under [More Sort Options]
3) Add in a [Calculated Field] with the formula "=COUNT('Cod Store' )"
4) Click on [Show Values As] and select [Running Total in] with Name as the [Base Field]
5) Filter the Rank column to show only the first 4 items
6) When the database is updated, refresh the PivotTable and do the filtering in Rank column again

quote insights from josephteh....
the bottom 4 filter in pivot should be able to help.
to use bottom 4 fliter,
1) go to the pivot created
2)go to the drop down box on Name
3) select Value filters then top 10 filter
4) change Top drop down to bottom
5) change 10 to 4
see attached.

Mistake(s) in final formula post #12.

Need two. In H5:H32.

Formula:

Please Login or Register  to view this content.

In I5:I32

Formula:

Please Login or Register  to view this content.

My apologies.