Google Sheets: Get First and Last Entry Rows for Each Item

With the help of either the QUERY function or a combination of UNIQUE and XLOOKUP, you can get the first and last entry rows for each item in Google Sheets. This method returns a summary with three columns: a list of unique items (e.g., SKUs), the row number of their first appearance, and the row number of their last appearance.

Manually scanning large datasets to find when each item first or last appeared can be tedious and error-prone. Automating this process makes your analysis faster, cleaner, and more accurate.

Table of Contents

Real-Life Use Cases: When to Get First and Last Entry Rows for Each Item

Here are a few practical scenarios where this method comes in handy:

Inventory & Stock Movement

Track when each product SKU first appeared and when it was last updated. Useful for identifying inactive products or analyzing stock movement timelines.

CRM & Sales Tracking

Summarize each customer’s first and last interaction to measure engagement duration, retention, or account inactivity.

Project & Task Tracking

Get the start and end rows for each task or project entry to monitor timelines and spot bottlenecks.

Financial Transactions

List the earliest and latest row of each recurring transaction (e.g., salaries, bills) for auditing or trend analysis.

Data Logging & Experiments

Capture the full entry range for each experiment run or subject to isolate complete datasets for deeper review.

Sample Inventory Data

Use the following example to try out the formulas:

Date	SKU	Action
2025-05-05	SKU-001	In
2025-05-07	SKU-002	In
2025-05-10	SKU-001	Out
2025-05-12	SKU-003	In
2025-05-13	SKU-002	Out
2025-05-14	SKU-001	In
2025-05-16	SKU-003	Out
2025-05-17	SKU-002	In
2025-05-18	SKU-003	In
2025-05-20	SKU-001	Out

In this dataset, each SKU may appear multiple times. Your goal is to get the first and last entry rows for each item (SKU).

Get First and Last Entry Rows for Each Item Using QUERY

To find the first and last row numbers for each SKU using a single formula, enter the following into cell E1:

=ArrayFormula(
   QUERY(
      HSTACK(ROW(B2:B), B2:B), 
      "select Col2, min(Col1), max(Col1) 
      where Col2 is not null 
      group by Col2 
      order by min(Col1) 
      label Col2 'SKU', min(Col1) 'First Row', max(Col1) 'Last Row'"
   )
)

Example Output: First and Last Entry Rows for Each SKU

Summary table of SKUs with first and last row numbers in Google Sheets

This tells us that SKU-001 first appeared on row 2 and last appeared on row 11, indicating a full activity range across the sheet.

How It Works:

HSTACK(ROW(B2:B), B2:B) pairs each SKU with its row number.
QUERY groups the SKUs and returns:
- The first row (min) each SKU appears on.
- The last row (max) each SKU appears on.
The results are sorted by first appearance and labeled clearly.

This gives you a quick summary table:
Get First and Last Entry Rows for Each Item — fast and formula-driven.

Get First and Last Entry Rows for Each Item Using UNIQUE + XLOOKUP

If you’re more comfortable with XLOOKUP than QUERY, follow these three simple steps:

Step 1: Get Unique Items

In E2, enter:

=UNIQUE(B2:B)

This lists each unique SKU from your data.

Step 2: Find the First Entry Row

In F2, enter:

=ArrayFormula(XLOOKUP(E2:E, B2:B, ROW(B2:B), "", 0, 1))

This searches for each SKU from top to bottom and returns the first matching row number.

Step 3: Find the Last Entry Row

In G2, enter:

=ArrayFormula(XLOOKUP(E2:E, B2:B, ROW(B2:B), "", 0, -1))

This searches from bottom to top and returns the last matching row number.

That’s it — you now have a clear view of when each SKU was first and last recorded.

Final Thoughts

Whether you use QUERY or XLOOKUP, both methods help you get the first and last entry rows for each item in Google Sheets with ease. This is a powerful technique for reporting, tracking, and analyzing anything that involves repeated entries over time.