Excel：分离并展示指定重复ID行（而非删除重复数据）

Hey there! Let's tackle this problem step by step. You've got two columns (ID and corresponding data), with duplicate IDs, and you need to separate out the yellow-highlighted rows containing B2 and A2 values—instead of just deleting duplicates. Here are a couple of solid approaches depending on the tool you're using:

Step 1: Filter the target rows #

• First, isolate the rows with B2/A2 in your data column using Excel's Filter feature:
  1. Select your entire data range (including headers).
  2. Go to the Data tab > click Filter.
  3. Click the dropdown arrow on your data column > hover over Text Filters > choose Contains.
  4. In the pop-up box, enter B2 and click OK. Repeat this for A2 (or use the Or condition to include both in one filter).

Step 2: Copy and separate the filtered rows #

• Once the yellow-highlighted (matching) rows are visible:
  1. Select all visible rows (skip the header if you don't want it copied).
  2. Right-click > choose Copy (or press Ctrl+C).
  3. Navigate to a new sheet or empty range > right-click > Paste (Ctrl+V).
  4. Clear the filter (Data tab > Filter again) to restore your original full dataset.

Bonus: Split based on duplicate IDs AND B2/A2 values #

• If you only want rows where the ID is duplicated and the data column has B2/A2, add a helper column:
  1. Insert a new column (e.g., Column C) next to your data.
  2. In cell C2, enter this formula (replace [last_row] with your data's final row number):

     =AND(COUNTIF($A$2:$A$[last_row],A2)>1,OR(B2="B2",B2="A2"))
     

  3. Drag the formula down to all rows—this returns TRUE for rows meeting both conditions.
  4. Filter Column C for TRUE, then copy those rows to your desired location.

If you prefer code to automate this, Pandas makes it straightforward:

1. Import Pandas and load your data:

   import pandas as pd
   
   # Replace 'your_file.csv' with your actual file path
   df = pd.read_csv('your_file.csv')
   

2. Filter rows containing B2 or A2 in the data column:

   # Replace 'data_column' with your data column's actual name
   target_rows = df[df['data_column'].str.contains('B2|A2', na=False)]
   

3. If you need to ensure these rows have duplicate IDs:

   # Get all duplicate IDs
   duplicate_ids = df[df.duplicated(subset='ID', keep=False)]['ID'].unique()
   # Filter rows with duplicate IDs AND B2/A2 values
   target_rows = df[(df['ID'].isin(duplicate_ids)) & (df['data_column'].str.contains('B2|A2', na=False))]
   

4. Save or view the separated rows:

   # Save to a new CSV file
   target_rows.to_csv('separated_rows.csv', index=False)
   # Or print the results
   print(target_rows)
   

That covers both manual and automated ways to separate your target rows without altering the original dataset. Let me know if you need clarification on any step!

内容的提问来源于stack exchange，提问作者Vincent Tang