Oct 12 2021 09:33 PM
I WANT TO MATCH 2 SHEET DATA TOGETHER TO GENERATE SHEET. BUT MONTH WILL EXPAND THE Column to 24 MONTH.
EXAMPLE SHEET 1 I HAVE DATA FOR 12 MONTHS 2020
SHEET 2 I HAVE DATA FOR MONTH 12, 2012
I WANT THE COMBINED SHEET TO BE DATA FROM JANUARY 2020 TO OCTOBER 2021 .
WHAT SHOULD I DO
THANKS REGARDS TEAM
E : thaitanthanh010@gmail.com
Oct 13 2021 04:59 AM
In my example names of my worksheets are "Tabelle14" and "Tabelle15" and range of data is A1:D12 in both sheets.
Please replace "Tabelle14" and "Tabelle15" in the formula with the names of your worksheets.
=IF(NOT(ISNA(VLOOKUP($A1,Tabelle14!$A$1:B$12,COLUMN(),FALSE))),VLOOKUP($A1,Tabelle14!$A$1:B$12,COLUMN(),FALSE),VLOOKUP($A1,Tabelle15!$A$1:B$12,COLUMN(),FALSE))
Enter manually January 2020 in cell A1 of a third sheet and copy down to A22, this will show October 2021 in cell A22.
Then enter above formula in B1 and copy formula across range B1:D22.
Oct 13 2021 11:31 AM
I understand that you only want to add data for items which are already in "vd 2021". For example dataset " 66700314 ĐỨC HƯNG 00, Cầu Cầm Pha,. Hòa Bình, Huyện Chợ Mới, An Giang Tiger Crystal Can 24s (330) " is only in "vd 2019" and should not be added to "vd 2021".
I made a copy of file "vd2021" and renamed it to "Trang tinh1".
Then i entered 01/10/2019 to 01/09/2020 in range T1:AE1.
In file "vd 2019" i entered an additional column right to column B and entered formula =NUMBERVALUE(B2) in cell C2 and copied formula down.
I entered formula below in cell T2 in worksheet "Trang tinh1" with ctrl+shift+enter and then filled it into range T2:AE1431.
=IFERROR(VLOOKUP($A2&$B2&$C2&$D2&$E2&$F2,CHOOSE({1.2},'vd 2019'!$A$2:$A$1219&'vd 2019'!$C$2:$C$1219&'vd 2019'!$D$2:$D$1219&'vd 2019'!$E$2:$E$1219&'vd 2019'!$F$2:$F$1219&'vd 2019'!$G$2:$G$1219,'vd 2019'!H$2:H$1219),2,0),"")
Then i crosschecked sum of ranges T2:T1431, U2:U1431, V2:V1431 and so on with sums of "vd 2019" and it seems to apply.