How to bulk update lot availability in Sublime or Google Sheets?

Other Questions Google Sheets
1

I’m dealing with a huge list of lots. There are about 3000 in total. Some get sold randomly. I have two columns:

A: All lots (marked -1 for available)
B: Sold lots

I need to update column A to show which lots are sold (-0) based on what’s in column B. Doing this one by one is taking forever!

Is there a quick way to do this in Sublime or Google Sheets? I’m not great with Google Sheets formulas or Sublime regex.

Here’s a simple example of what I’m working with:

A       B
101 -1  101
102 -1  105
103 -1  110
104 -1
105 -1

After updating, A should look like:

101 -0
102 -1
103 -1
104 -1
105 -0

Any tips would be awesome! Thanks!

2

I’ve faced a similar challenge with bulk updates before, and I found Google Sheets to be a lifesaver for this kind of task. Here’s a simple formula that should do the trick for you:

In column C (assuming your data starts in row 2), put this formula:

=IF(COUNTIF($B$2:$B$3001,A2)>0,“-0”,“-1”)

Then just drag it down for all 3000 rows. This formula checks if the lot number in column A appears anywhere in column B. If it does, it returns “-0” (sold), otherwise “-1” (available).

Once you’ve applied this to all rows, you can copy the results and paste-special (values only) over your original column A. This method is much faster than manual updates and should save you hours of work. Hope this helps!

3

hey there! google sheets is ur best bet here. try this:

in C2, put: =IF(COUNTIF($B$2:$B$3001,A2)=0,“-1”,“-0”)

drag down. itll check if A is in B, marking -0 if sold, -1 if not. copy C, paste values over A. done in minutes!

hope this helps ya out!

4

For bulk updates like this, Google Sheets is definitely the way to go. You can use a VLOOKUP function to quickly match and update your data. Here’s how:

  1. In a new column (let’s say C), use this formula:
    =IF(ISNA(VLOOKUP(A2,$B$2:$B$3001,1,FALSE)),‘-1’,‘-0’)

  2. Copy this formula down for all 3000 rows.

  3. Copy column C and paste values over column A.

This approach is efficient and doesn’t require advanced formula knowledge. It searches column B for each value in A, marking ‘-0’ if found (sold) and ‘-1’ if not (available). The ISNA function handles cases where the lot isn’t found in the sold list.

Remember to double-check a few rows after updating to ensure accuracy.