如何在Excel或SQL Server中实现基于前两词匹配的Reference自动增量赋值？

Absolutely, you can automate this with either Excel or SQL Server—let’s walk through both approaches so you can pick what works best for you.

The core idea is to group records by the first two words of the Name column, then build the incrementing reference using the group's starting Current Reference value.

Step 1: Extract the first two words (group key) #

Add an auxiliary column (e.g., column B) to capture the first two words of each Name:

• For Excel 365/2021 (modern versions):

  =TEXTBEFORE(A2," ",2)
  

  This function directly grabs all text up to the second space, handling extra spaces or trailing symbols seamlessly.
• For older Excel versions (no TEXTBEFORE):

  =IF(ISERROR(FIND(" ",A2,FIND(" ",A2)+1)),A2,LEFT(A2,FIND(" ",A2,FIND(" ",A2)+1)-1))
  

  This checks if there’s a second space; if not (single-word names), it returns the full name. Otherwise, it extracts text up to the second space.

Step 2: Calculate the desired Reference #

In the DESIRED Reference column (e.g., column D), use this formula:

=XLOOKUP(B2,$B$2:$B$6,$C$2:$C$6,"",,1)+(COUNTIF($B$2:B2,B2)-1)*0.1

Breakdown of the formula:

• XLOOKUP(B2,$B$2:$B$6,$C$2:$C$6,"",,1): Finds the first Current Reference value in the group (the base value for incrementing).
• COUNTIF($B$2:B2,B2)-1: Counts how many times the group key has appeared up to the current row. Subtract 1 because the first record doesn’t need an increment.
• Multiply by 0.1 and add to the base value to get the incremented reference.

If your data is already sorted by group (all matching first-two-word records are together), you can simplify to:

=C2+(COUNTIF($B$2:B2,B2)-1)*0.1

Window functions and common table expressions (CTEs) make this straightforward. We’ll group records by the first two words, assign row numbers within each group, then compute the desired reference using the group’s starting value.

Here’s the full query, with handling for single-word names:

WITH NameGroups AS (
    SELECT 
        Name,
        CurrentReference,
        -- Create group key from first two words
        CASE 
            WHEN LEN(Name) - LEN(REPLACE(Name, ' ', '')) < 1 THEN Name  -- Handle single-word names
            ELSE SUBSTRING(Name, 1, CHARINDEX(' ', Name, CHARINDEX(' ', Name) + 1) - 1)
        END AS GroupKey,
        -- Assign row number within each group (ordered by Name)
        ROW_NUMBER() OVER (
            PARTITION BY 
                CASE 
                    WHEN LEN(Name) - LEN(REPLACE(Name, ' ', '')) < 1 THEN Name
                    ELSE SUBSTRING(Name, 1, CHARINDEX(' ', Name, CHARINDEX(' ', Name) + 1) - 1)
                END 
            ORDER BY Name
        ) AS RowNum
    FROM YourTable  -- Replace with your actual table name
),
GroupBaseReferences AS (
    -- Get the starting reference value for each group
    SELECT 
        GroupKey,
        MAX(CASE WHEN RowNum = 1 THEN CurrentReference END) AS BaseReference
    FROM NameGroups
    GROUP BY GroupKey
)
SELECT 
    ng.Name,
    ng.CurrentReference,
    gb.BaseReference + (ng.RowNum - 1) * 0.1 AS DESIREDReference
FROM NameGroups ng
INNER JOIN GroupBaseReferences gb ON ng.GroupKey = gb.GroupKey
ORDER BY ng.Name;

Logic breakdown: #

1. NameGroups CTE: Extracts the group key and assigns a row number to each record within its group.
2. GroupBaseReferences CTE: Captures the first CurrentReference value for each group (the base increment value).
3. Final query: Joins the two CTEs to calculate the desired reference by adding (RowNum - 1)*0.1 to the group’s base value.

If your Name column has multiple consecutive spaces, add REPLACE(Name, ' ', ' ') to clean up spaces before extracting the group key.

内容的提问来源于stack exchange，提问作者Ryan Jackson