Barcode Check out from an inventory

[Ehunt256]

OK, Greetings all I'm new her and can use some serious help.
My knowledge on Excel and related to it is limited so please bear with me.

My issue is this, I want to create a Barcode/QR code Check in and out sheet.
By scanning my bar/QR code it will check my inventory sheet for a SN if it is on the sheet, the SN time and date OUT for the item is shown once scanned again it checks IN the item and makes a time and date. Of course having the person to type their name and last initial can help with but I just want to get this started first before asking difficult assistance. If anything is needed to help please let me know.

Sorry for the long post, I just really need help here.

 

[shamimarita]

Hello Ehunt256,

Welcome to the ExcelDemy community! Thank you for explaining your situation clearly, no need to apologize, we're all here to learn and help each other out.

You're on a great path with your idea for a barcode/QR code-based check-in/check-out system. Excel can definitely support a system like this with a combination of formulas and maybe some VBA (macros) if needed.

To get started, here’s a simple approach:
Barcode/QR Scanner: Most scanners act like a keyboard, so when you scan, it enters the value into the active cell.
Check-In/Out Sheet:

• Have a cell where the scanned SN is entered.
• Use VLOOKUP or INDEX-MATCH to find the SN in the inventory.
• Use formulas or VBA to record the current date and time for "Out" when the SN is first scanned, and for "In" on the second scan.

Inventory Sheet: List all Serial Numbers (SN), item names, and current status.
CheckLog Sheet: When you scan an SN, it goes into a log with the timestamp (=NOW()), and a formula alternates between “Out” and “In”:
=IF(COUNTIF($B$2:B2,B2) MOD 2 = 1, "Out", "In")
Status Update: Use a LOOKUP formula in your Inventory sheet to show the latest action and time.
=IFERROR(LOOKUP(2,1/(CheckLog!B$2:B$1000=A2), CheckLog!C$2:C$1000), "Never Scanned")

Last Action Time:
=IFERROR(LOOKUP(2,1/(CheckLog!B$2:B$1000=A2), CheckLog!A$2:A$1000), "")

• Replace A2 with the cell holding the SN in the inventory table.

 

[Ehunt256]

THANKYOU SO MUCH for you help and response here's what I have now. I already completed an Inventory sheet. I can post that too if needed.

Now what you sent is that VBA script or a formula for the button, sorry I got a little lost there. If what you sent is what is needed, then I can figure it out.

 

[shamimarita]

Hello Ehunt256,

Thank you again for the update and for sharing your screenshot. Your workbook is coming together beautifully!

To clarify your last question:
In my earlier response, I focused only on formula-based solutions, no VBA was included. The intent was to help you get started without needing macros, using functions like COUNTIF, LOOKUP, and NOW() to track check-in/check-out actions. This is perfect if you're aiming to avoid enabling macros or if you're still getting comfortable with Excel.

Here’s a quick recap of what we did with formulas:
Use of =NOW() to timestamp scans.
Use of COUNTIF to alternate between "Out" and "In":
=IF(COUNTIF($B$2:B2,B2) MOD 2 = 1, "Out", "In")
Use of LOOKUP to track the latest action and time from your log.

Now, if you're ready to automate this process further...
Connect your "Scan" button to a VBA (macro) script. This will automatically:

• Detect if the scan is a new Check-Out or a returning Check-In
• Log the time, calculate duration, and clear the input box
• Display a message confirming the action

Here’s the VBA code that powers the button:

Private Sub CommandButton1_Click()
    Dim sn As String
    Dim wsLog As Worksheet
    Dim lastRow As Long
    Dim currentTime As String
    
    Set wsLog = ThisWorkbook.Sheets("Log")
    sn = wsLog.Range("B3").Value ' Scan input cell
    currentTime = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
    
    If sn = "" Then
        MsgBox "Please scan a barcode."
        Exit Sub
    End If
    
    ' Check if item was already scanned out
    Dim foundRow As Long
    Dim i As Long
    foundRow = 0
    For i = wsLog.Cells(wsLog.Rows.Count, "B").End(xlUp).Row To 3 Step -1
        If wsLog.Cells(i, 2).Value = sn Then
            foundRow = i
            Exit For
        End If
    Next i
    
    If foundRow > 0 And wsLog.Cells(foundRow, 4).Value = "" Then
        ' Log Check-In
        wsLog.Cells(foundRow, 4).Value = currentTime
        wsLog.Cells(foundRow, 5).Formula = "=D" & foundRow & "-C" & foundRow
        MsgBox "Checked IN successfully!"
    Else
        ' Log Check-Out
        lastRow = wsLog.Cells(wsLog.Rows.Count, "B").End(xlUp).Row + 1
        wsLog.Cells(lastRow, 2).Value = sn
        wsLog.Cells(lastRow, 3).Value = currentTime
        MsgBox "Checked OUT successfully!"
    End If
    
    wsLog.Range("B3").Value = "" ' Clear scan box
End Sub

To use this:

• Turn on Developer > Design Mode
• Right-click the Scan button > choose View Code
• Paste the code in the opened VBA editor
• Save as .xlsm (Macro-Enabled Workbook)

This VBA solution is optional and builds directly on your formula-based system. You can choose whichever method suits your workflow best!

 

[Ehunt256]

I'm sorry for not fully understanding, but what you have here is both formula (Which I'm extremely grateful for)
and VBA for advanced marco function.
Thank you so much for taking the time to show me how to do this
but my last question where to put the formulas?

 

[shamimarita]

Hello Ehunt256,

No need to apologize at all, your curiosity and progress are awesome, and I’m really glad to help you along the way! You're right, the earlier replies include both formulas (for a no-code setup) and VBA (for automation via macro buttons). You can start with formulas, and later decide if you want to enhance them with macros. Here's how to place the formulas step by step:
Formula Setup (No Macros Needed):
1. CheckLog Sheet: This is the sheet that logs each scan with time and action:

• Column A – Timestamp:
  Use =NOW() whenever a scan is recorded (can be done manually or with VBA later).​
• Column B – Serial Number (SN):
  This is where the scanned value goes.​
• Column C – Action ("Out" or "In"):
  Paste this formula in C2 and drag it down:​

=IF(COUNTIF($B$2:B2,B2) MOD 2 = 1, "Out", "In")

2. Inventory Sheet: This sheet tracks your master list of items and shows their latest status.

• Column A – SN
• Column B – Item name
• Column C – Last Action (e.g., "In" or "Out"):
  Paste this formula in C2:

=IFERROR(LOOKUP(2,1/(CheckLog!B$2:B$1000=A2), CheckLog!C$2:C$1000), "Never Scanned")

• Column D – Last Scan Time:
  Paste this formula in D2:

=IFERROR(LOOKUP(2,1/(CheckLog!B$2:B$1000=A2), CheckLog!A$2:A$1000), "")

Make sure to update A2 in each formula to the correct SN cell in your Inventory sheet.

 

[Ehunt256]

I'm sorry but I'm not getting the results that is indicated. Nothing has worked The checkout sheet (Log) is where the lookup or search would take place, it would go to (Inventory) SN column D2 search If found It will be placed in the Scan column of the (Log) sheet and the =NOW for (OUT) will be shown if already out the =Now will be for (IN) also if needed I'll re upload the sheet if needed and I plan on using this without Macros so I guess the button isn't needed. Sorry I'm still lost more than ever now.

 

[Ehunt256]

Nothing seems to work right

 

[shamimarita]

Hello Ehunt256,

As mentioned earlier, your system can be built using formulas (no code) or VBA macros (automation). Since you’re focused on the formula-based approach right now, here’s a full guide on where to place the formulas, plus helpful learning links so you can explore and build at your own pace.

Step-by-Step: Formula-Based Check-In/Check-Out System

1. Check Log Sheet

• Column A (Timestamp): Use this formula when scanning:

=NOW()

(This will return the current date and time. You can paste it manually or auto-fill it with a macro later.)

• Column B (Serial Number):
  The barcode scanner will input here.
• Column C (Action – In/Out): To alternate between “Out” and “In”:

=IF(COUNTIF($B$2:B2,B2) MOD 2 = 1, "Out", "In")

2. Inventory Sheet

• Column A – SN
• Column B – Item Name
• Column C – Last Action:

=IFERROR(LOOKUP(2,1/(CheckLog!B$2:B$1000=A2), CheckLog!C$2:C$1000), "Never Scanned")

• Column D – Last Scan Time:

=IFERROR(LOOKUP(2,1/(CheckLog!B$2:B$1000=A2), CheckLog!A$2:A$1000), "")

Replace A2 with the actual SN cell for each row.

Helpful Tutorials from ExcelDemy:

Here are articles that go deeper into these topics, with visuals and step-by-step breakdowns:

How to Use Barcode Scanner for Inventory in Excel
Learn how to set up your scanner and log data correctly.

 

[Ehunt256]

Sorry I must be missing something I keep getting the same thing just time placed on all points and one in and that's it

 

[Ehunt256]

Hello Ehunt256,

As mentioned earlier, your system can be built using formulas (no code) or VBA macros (automation). Since you’re focused on the formula-based approach right now, here’s a full guide on where to place the formulas, plus helpful learning links so you can explore and build at your own pace.

Step-by-Step: Formula-Based Check-In/Check-Out System

1. Check Log Sheet

• Column A (Timestamp): Use this formula when scanning:

=NOW()

(This will return the current date and time. You can paste it manually or auto-fill it with a macro later.)

• Column B (Serial Number):
  The barcode scanner will input here.
• Column C (Action – In/Out): To alternate between “Out” and “In”:

=IF(COUNTIF($B$2:B2,B2) MOD 2 = 1, "Out", "In")

2. Inventory Sheet

• Column A – SN
• Column B – Item Name
• Column C – Last Action:

=IFERROR(LOOKUP(2,1/(CheckLog!B$2:B$1000=A2), CheckLog!C$2:C$1000), "Never Scanned")

• Column D – Last Scan Time:

=IFERROR(LOOKUP(2,1/(CheckLog!B$2:B$1000=A2), CheckLog!A$2:A$1000), "")

Replace A2 with the actual SN cell for each row.

Helpful Tutorials from ExcelDemy:

Here are articles that go deeper into these topics, with visuals and step-by-step breakdowns:

How to Use Barcode Scanner for Inventory in Excel
Learn how to set up your scanner and log data correctly.

1 Log Sheet
=NOW()
Just records time when something is entered

=IF(COUNTIF($B$2:B2,B2) MOD 2 = 1, "Out", "In")
It just leaves an "In" Thats it

Everything else just accepts and no change

So maybe I didn't explain well so the "Blue" sheet is the Log in and out sheet
What I need it to do is once C1 has a barcode OR QR code enter it will look at the Inverntory sheet "Yellow"
Look up from D2 on down and find a SN if not the a popup tell us not in system. If it is in the system it will check OUT the item and give the date and time, but if scanned again it will put the current time on it's IN window.
Sorry for the confusion and lack of understanding, but I have a feeling I'm missing something simple you are telling me it's just tough to understand oh Formulas are pretty easy to understand and easy to program so for the sake of this workbook can we keep it like that?

And thank you so much for all your help?

 

[Ehunt256]

Say what about a check list?
So now I have an inventory sheet what if I just scan if it is on the list it conforms with a check
otherwise it records the serial number. I see now to try to start smaller then try to build up to the current task.

 

[shamimarita]

Hello Ehunt26,

Simplify with a Checklist Logic. Let’s start small and build:

Step 1: Create Your Inventory Table (Inventory Sheet)
Let’s assume:

• Column A: Serial Number (SN)
• Column B: Item Name
• Column C: Status (Check-out or Check-in)
• Column D: Last Action Time

Step 2: On the Log Sheet

• Cell C1 → This is where the barcode scanner enters the value.
• In C2 (or nearby cell), enter this formula to check if the SN exists in the Inventory:

=IF(COUNTIF(Inventory!A:A, C1) > 0, " Found in Inventory", " Not Found")

If you want to display the row number from the inventory:
=MATCH(C1, Inventory!A:A, 0)

To record Out time only if blank, you could use:
=IF(AND(C2=" Found in Inventory", Inventory!C2=""), NOW(), "")

(But this would work better if we let the scanner trigger a macro — we’ll stick to manual paste/update for now.)

Checklist System
Based on your last comment, a great idea is to use a checklist system:

• When a barcode is scanned into C1, Excel checks the Inventory list.
• If it exists, it marks in the checklist.
• If not, it logs the SN in a separate list (maybe an "Unrecognized" sheet).

Formula for checklist in Inventory sheet:
In Column E (Checklist):
=IF(A2=Log!$C$1, " Scanned", "")
Now, whenever you scan something, it flags it in the inventory.

 

[Ehunt256]

OK Thanks for all your help I'm confident that this one can work well.

 

[Ehunt256]

=IF(COUNTIF(Inventory!A:A, C1) > 0, " Found in Inventory", " Not Found")
in the formulas how to make the check mark and x mark?

 

[shamimarita]

Hello Ehunt256,

To insert these marks, you have two easy options:

 

[Ehunt256]

Ah ok thanks sorry for the lack of knowledge

 

[Ehunt256]

Ok I found a perfect reference

Here is what I'm trying to create via formulas

 

[CHATgptTUnelGog PRO]

Повышайте эффективность работы с GPTunnel — ваш прямой мост к искусственному интеллекту! https://gptunnel.ru/?ref=ru

Представляем GPTunnel.ru — надежный сервис для доступа к передовым нейросетям
Устали от ограничений и блокировок, мешающих вашему бизнесу развиваться в цифровую эпоху? GPTunnel.ru предлагает простое и элегантное решение для использования самых продвинутых технологий искусственного интеллекта без дополнительных сложностей!

Что вы получаете с GPTunnel ru?

— Стабильный доступ к ведущим нейросетям — работайте с ChatGPT, Midjourney, Claude и другими AI-инструментами без перебоев
— Полная доступность в России — сервис официально работает на территории РФ без каких-либо ограничений
— Удобная оплата российскими картами — принимаем карты МИР, Visa и Mastercard российских банков
— Экономия времени и ресурсов — никаких технических настроек или дополнительного оборудования
— Масштабируемое решение — подходит как для индивидуальных предпринимателей, так и для крупных компаний
— Безопасность и конфиденциальность — ваши данные под надежной защитой

Кому необходим GPTunnel?
— Маркетологам и копирайтерам — для создания качественного контента
— Аналитикам и исследователям — для обработки массивов информации
— Предпринимателям — для оптимизации бизнес-процессов
— IT-специалистам — для ускорения разработки
— Дизайнерам — для быстрой генерации идей и визуальных концепций

Специальное предложение!
— Прямо сейчас получите 10% скидку на любой тарифный план по промокоду RU10

— Бонус для новых пользователей — бесплатный 3-дневный тестовый период для оценки всех возможностей сервиса

— Не упустите возможность вывести свой бизнес на новый уровень с помощью искусственного интеллекта!

Зарегистрироваться на GPTunnel: https://gptunnel.ru/?ref=ru Промокод: RU10 дает 10% скидку!

С GPTunnel.ru будущее технологий доступно вам уже сегодня — без ограничений, с российскими платежными методами и официальной поддержкой на русском языке!

 

[Ehunt256]

I understand now this cannot be done via Formulas it needs to be done via VBA now I got a little experience with it so can it get help that method?