update data in one gogle sheet by comapring and referencing from other sheet

Question

I need to update a cell with defined value automatically by comparing and referencing data from other google sheet . In sheet1 Column_C to be updated by comparing column_A from sheet1 and sheet2, if its matched and Column_B value from Sheet2 is 1 than result should be issued else in-stock whether Column_A matching in both sheets or not and whatever value is in Column_B from sheet2 except value 1

Sharing google sheet link https://docs.google.com/spreadsheets/d/1h_D5u16ye1CA6C7dideJVZoV8cBvZB8M3RRxXBANsf4/edit?usp=sharing

Aresvik Aresvik · Accepted Answer · 2021-07-12T12:40:33

Try this in cell C1 (delete all other values below):

=arrayformula({"Status";if(A2:A<>"",substitute(substitute(iferror(vlookup(A2:A,filter(sheet2!A:B,countifs(sheet2!A:A,sheet2!A:A,row(sheet2!A:A),">="&row(sheet2!A:A))=1),2,0),),1,"Issued"),0,"In-Stock"),)})

To handle items in sheet1 that are not in sheet2, you can add a value in the iferror function (the first "In-Stock" below):

=arrayformula({if(A2:A<>"",substitute(substitute(iferror(vlookup(A2:A,filter(sheet2!A:B,countifs(sheet2!A:A,sheet2!A:A,row(sheet2!A:A),">="&row(sheet2!A:A))=1),2,0),"In-Stock"),1,"Issued"),0,"In-Stock"),)})

This is responsible for getting the dataset from sheet2:

filter(sheet2!A:B,countifs(sheet2!A:A,sheet2!A:A,row(sheet2!A:A),">="&row(sheet2!A:A))=1)

This part gets the instance number of each duplicate value in sheet2!A:A:

countifs(sheet2!A:A,sheet2!A:A,row(sheet2!A:A),">="&row(sheet2!A:A))

Where there are duplicate values, ">=" gets the one furthest down the sheet. Changing it to "<=" will get the first instance at the top of the sheet.

Within filter, countifs(sheet2!A:A,sheet2!A:A,row(sheet2!A:A),">="&row(sheet2!A:A))=1) filters the column to show just 1 instance of each value in sheet2!A:A.

Looking at your example sheet, you have these values in row 6, 7, 8, so the first instance (ascending or descending) will be 0:

1230E   0
1230E   1
1230E   0

If you're looking for a different logic, like 1 trumps 0, then you'll need to apply a sort on the data before performing the vlookup. Something like:

=arrayformula({if(A2:A<>"",substitute(substitute(iferror(vlookup(A2:A,unique(sort(sheet2!A2:B,1,1,2,0)),2,0),"In-Stock"),1,"Issued"),0,"In-Stock"),)})

update data in one gogle sheet by comapring and referencing from other sheet

1 Answers