Need help with excel formulas

Copper Contributor

So I am currently trying to digitalize a few things at work and I am having trouble finding a formula that will automatically take 4 of the most recent months and there data and move it to another sheet only if it has numbers that are not 0 the chart is a simple one with one column with months and the other is with the quantity of parts inspected.

Sorry if its not very clear

20210520_125810.jpg

 

4 Replies
1 - What version of Excel do you run?
2 - In your pic only 3 "months" with Qty > 0 and you want to take the 4 most recent "months". So, what should happen in the current scenario (only 3 "months")?

@L z. Im running the 2016 version and i have a table on a different sheet that has 3 periods(Months) and one for the current month i would like to be able to have the 2 or 3 if there is one older months go into the 3 older period spots and if there is only 2 it would just leave one of the spots blank and with the current month will get put into the current month spot if that is possible

@TMan5757 

I would recommend (easier & more clear) that you format the 2 columns range showed in your picture as a Table and name the columns Month & QTY (as in your pic.). Now, let's assumed it's named Table1. In your other sheet, (not mandatory but more clear), name the cell (i.e. CurrentMonth) with your current month

 

Demo.png

in B2 and copy down until B4 (Edited as initial didn't work in some scenario):

 

=IF(
 ROWS(B1:B$3) > COUNTIFS(Table1[Month],"<" & CurrentMonth, Table1[QTY],">0"), "",
 AGGREGATE(14,6,Table1[Month]/((Table1[Month] < CurrentMonth)*(Table1[QTY] > 0)),ROWS(B1:B$3))
)

 

in C2 and copy down until C4:

 

=IF(B2 = "", "", INDEX(Table1[QTY], MATCH(B2,Table1[Month])))

 

 

Corresponding sample is attached

@TMan5757 

Two solutions depending on your version.