match between two workbooks and show different values

[abdo meghari]

hi
I have two files every month . I have to create report shows the sales and arrives for many types of tires . the file FL1 is the truth values for many brands and the file FL2 . I put the values manually based on file FL1. so what I want creating summary report show the differnt values whether surplus or deficit . it should match the COL A,B,C in FL1 with COL B,C,D in file FL2 and subtract the values in in last COL STOCK in month JAN in file FL2 from COL BALANCE in file FL1 if the values in file FL2 bigger than values in file FL1 then it shows in COL SURPLUS in file OUTPUT otherwise show in COL DEFICIT ,also show the real values in COL TRUTH based on COL BALANCE in file FL1 and if there items are existed in file FL1 and not existed in file FL2 then should highlight in file OUTPUT and add it based on match COL A.
last thing when create the summary for specific month it should match cell value in F1 in file FL1 with cells value contain the header JAN or FEB ...and so on in file FL2 so the cell value in file FL2 changes in location when match with cell value F1 in file FL1
note: when issue months form JAN to DEC i will issue again after DEC start from JAN to DEC repeatedly so if when create again JAN month and data has already existed then should ignore them. just search for a new month JAN which there is no values are filled then should fill . I hope this point is clear .
thanks in advance

[DavidBowman]

Here is something that will help you get started. The summary formulas for January are done but you will need to fill out the rest of the sheet. The source data is all available on the Helper sheet so that should be easy now. There are some remaining questions. I have assumed you are going to add a sheet in FL1 for each month. You may have to change the formulas a little if that is not the case but what I have given you is enough for you to figure that out. Also your plan to just keep adding months with the same name probably needs to be revisited. Having a sheet for each year would make more sense.

[abdo meghari]

thanks but I wish do that by vba . despite of it's not easy
and if there is any details want to know just inform me if you can help by vba .

[abdo meghari]

is there way do that by vba ?

[karmapala]

I'm not sure if this will work,
but maybe you can have a look.

[abdo meghari]

@karmapala

thanks for your trying . but I have some notes
cell value F1 in file FL2 changes in location when match with cell value G1 in file FL1 .so the FEB in file FL2 is I1 and MAR in FL2 is L1 and so on for rest the months are changable . every time it will insert columns for specific month so it will change in location . it's not always in CELL F1 , and the category , why you repeat category for each item ? I think every category ends at row LTT . you can specify category from row 2 to end row LTT instead of repeate writting category for each item .
if you see this is difficult to deal with it . do what you see fitting
about the values in file output seems to work and this is what I want but the problem it will work for one month and specific cell F1
thanks in advance

[karmapala]

@karmapala

thanks for your trying . but I have some notes
cell value F1 in file FL2 changes in location when match with cell value G1 in file FL1 .
so the FEB in file FL2 is I1 and MAR in FL2 is L1
and so on for rest the months are changable .

I know that.
What I mean is the code I made need the placement of the month name is consistent,
which is always above the "middle" column.

I see that on each month, it will always have 3 columns header :
FL2 : the columns are ARRIVE SALE STOCK
Output : the columns are SURPLUS DEFICIT TRUTH.

So, the code I make need a pointer and that must be consistent which located in the "middle" of those 3 columns.

When the code working in FL2, when it look for the month name then found it in WHATEVER column,
it will create a variable to get where is the column of the STOCK of the regarding month
so then the found cell offset 1 to the right will certainly is the STOCK column.
So in the case of FL2, all month name must be above the SALE column-header
whatever where the columns are but it has to be on the above SALE column-header/

But if the next month you put the month name (for example) above of the first of those 3 columns (FL2, above ARRIVE column-header),
then the code will get a wrong pointer for the STOCK column, because the code is always 1 column offset to the right to get the STOCK columns.

every time it will insert columns for specific month so it will change in location .

I know that.
I know that the table will grow to the right, it will grow 3 columns to the right on each month.
But once again, each time it grows 3 column to the right, the code need the name of the month on the "middle" of those 3 columns.

So, the text JAN must be in cell F1
text FEB must be in cell I1
text MAR must be in cell L1
text APR must be in cell O1
and so on in the case of FL2.

The same thing with the Output wb.
the text of the month must be alwasy above the "middle" column,
the DEFICIT header-column in this case.

it's not always in CELL F1

I know that.
I know IT'S NOT that JAN in cell F1, then the next month - FEB will in cell F1,
the next month again, MAR again in cell F1. I know it's not like that.

and the category , why you repeat category for each item ?

Because the code need a unique value for each row.

I think every category ends at row LTT

NOT THE THING WHICH I SAW IN YOUR WB.
Have a look at this :
EXCEL_2021-08-17_15-50-22.png

The image above is the Output wb,
but it's exactly the same thing in FL2.
I can't find a consistency of the location of each cat.

you can specify category from row 2 to end row LTT instead of repeate writting category for each item

Then you can make it like that and then you can change the code.

the problem it will work for one month and specific cell F1

The code is not meant for ONE MONTH only
the code is not meant to "see" to only cell F1

The code assume that there'll be a list of month name in whatever column of row1 in FL2 and Output wb,
and the code assume that under each month there will always be three columns header.
the code assume that the name of any month in whatever column of row 1 will always be above the "middle" of those three columns header....
which is the SALE header in the case of FL2 and DEFICIT header in the case of Output wb.

the problem it will work for one month and specific cell F1

The problem is maybe you don't look at the code at all.
You maybe don't even try to make a new input for FEB to see whether the code runs properly or not.

[abdo meghari]

The code is not meant for ONE MONTH only
the code is not meant to "see" to only cell F1

The code assume that there'll be a list of month name in whatever column of row1 in FL2 and Output wb,
and the code assume that under each month there will always be three columns header.
the code assume that the name of any month in whatever column of row 1 will always be above the "middle" of those three columns header....
which is the SALE header in the case of FL2 and DEFICIT header in the case of Output wb.

it should work without problem ,but when change the cells G1 = FEB in FL1 and its sheet NAME is FEB . should copy to MONTH FEB in file OUTPUT ,but it gives error subsript ou of range in this line

[abdo meghari]

another option professionals ?