May 20 2021 10:06 AM
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
May 20 2021 10:36 AM
May 21 2021 05:57 AM
@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
May 21 2021 07:54 AM - edited May 22 2021 09:12 AM
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
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
May 21 2021 08:27 AM
Two solutions depending on your version.