ID all rows with same value in first cell

Question

ID all rows with same value in first cell

André Boshoff 20

I want to identify all rows with same value in first cell, then pick up those rows to do a vlookup on all the rows where the values are found

0 comments

Answer accepted by question author

4 additional answers

Your answer

Answer 1

HansV 462.6K MVP Volunteer Moderator

If you have a recent version of Excel, you can use the FILTER function, if necessary in combination with CHOOSECOLS.

Let's say your data are on a sheet named Detail Sheet, in A2:K100.

On the other sheet, you enter a family key in B1.

In another cell, say in A3, enter the formula

=FILTER('Detail Sheet'!A2:K100, 'Detail Sheet'!A2:A100=B1, "No Match")

If you want to return values from selected columns only, say columns B, C, E and H (column numbers 2, 3, 5, 8):

=IFERROR(CHOOSECOLS(FILTER('Detail Sheet'!A2:K100, 'Detail Sheet'!A2:A100=B1), 2, 3, 5, 8), "No Match")

André Boshoff 20 Reputation points

2025-07-27T11:05:20.9833333+00:00

Hi HansV

Thank you for the speedy help.

Herewith some additional information. I don't think I was clear enough. Sorry.

I have another sheet which is an input sheet in which I can type the "FamilyKey" (which is unique) to give me info about all the family members with that key value.

The fields that need to be populated by the enquiry are more or less the same for each family member, so what the formula must do is to read all the rows with the manually provided "FamilyKey", then populate the fields e.g. the main family member's name, surname, DoB, gender, age etc. but also for the spouse, child1, child2 etc.

In other words all the fields that need to be populated are dependant on the value of the "FamilyKey" provided. Each field that gets back info from the first sheet must be able to read the "FamilyKey" on the different rows. For each family member the info is always in the same column but in a different row. Therefore the formula must know by the "FamilyKey" how many family members (rows) there are in the Peope detail sheet and then retrieve the same info (column) from each different row for the number of times that there are "FamilyKey" entries. In other words if a family has three members there will be three rows but a family of seven there are seven rows. The formula must be able to:

a. recognise how many rows (family members) there are

b. know from which row the information must be retrieved, so main member is the 1st row, child 2 is the 4th row etc.

Therefore I need one formula that I can use in each field where infor comes back into. It must simply look at the relevant column (probably with VLOOKUP? e.g. Gender) but from the accurate row.

Alternativey I can send you my Excel spreadsheet to look at?

Thanks

André
HansV 462.6K Reputation points MVP Volunteer Moderator

2025-07-27T11:33:52.4233333+00:00

Could you create a stripped-down copy of the workbook demonstrating the problem (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

Alternatively, you can send it to hans dot vogelaar at gmail dot com
André Boshoff 20 Reputation points

2025-07-28T19:33:57.5766667+00:00

Thank you Hans

I sent an email with the OneDrive link for you.
HansV 462.6K Reputation points MVP Volunteer Moderator

2025-07-28T21:12:05.93+00:00

I have replied to your email message.

Answer 2

HansV 462.6K MVP Volunteer Moderator

The solution turned out to be formulas like this:

=FILTER('People Detail'!$B$4:$B$39, ('People Detail'!$A$4:$A$39=$E$1)*('People Detail'!$G$4:$G$39="Main"), "")

=FILTER('People Detail'!$B$4:$B$39, ('People Detail'!$A$4:$A$39=$E$1)*('People Detail'!$G$4:$G$39="Spouse"), "")

etc.

0 comments

Answer 3

André Boshoff 20

Thank you so much for your great support Hans!

The solution you provded is working very well.

0 comments

Answer 4

André Boshoff 20

Hi HansV

Thank you for your respons. Appreciated!

Could you, for additional information from my side please also refer to the additional note to Marcin perhaps?

0 comments

Answer 5

Marcin Policht 87,815 MVP Volunteer Moderator

Let's consider the following example:

A (Key)	B (Name)	C (Score)
101	Alice	85
102	Bob	90
101	Carol	95
103	Dave	88
102	Eve	91

Step 1: Identify duplicates in column A

In column D, write the following formula:

=COUNTIF(A:A, A2)>1

This returns TRUE for rows where A appears more than once.

Step 2: Filter for duplicate keys

Select your table (A1:D6 in the example).
Turn it into a Table (Ctrl+T).
Filter Column D to show only TRUE (duplicate keys).

Step 3: VLOOKUP using the duplicates

Now let's say you want to VLOOKUP the Score (C) for each Name (B) based on these duplicates.

In Column E, use:

=VLOOKUP(B2, B:C, 2, FALSE)

This looks up the Name (B2) in Column B and returns the Score (Column C). It works for the rows where duplicates were found in Column A.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

André Boshoff 20 Reputation points

2025-07-26T11:54:28.1266667+00:00

Mercin,

further to my reply to you...

I have a worksheet with details of people. Each family member has its own row of data.

In column A, I created a unique key made up of a concatenation value of the name and surname strings per family. So each family has the same key. Some families consist of 2 members and others up to seven.

I then have a separate input sheet in which I enter the key of a family and then it must show me selected details per family member. I therefore need the formula to know which rows have the same key so hat I can do the necessary lookup of the data in those rows.

I can therefore not manually intercpt the "process" for example to create a table. This must all be done in one Excel forumay / query.

Thank you for helping
Marcin Policht 87,815 Reputation points MVP Volunteer Moderator

2025-07-26T13:53:35.16+00:00
Assuming the main data is in Sheet1!A2:E100:

A = FamilyKey

B = Name

C = Age

D = Gender

E = Relationship

In your Input Sheet, enter the family key in A2.

In cell A5, use this formula:

=FILTER(Sheet1!B2:E100, Sheet1!A2:A100 = A2, "No family members found")

This will return all rows from columns B to E (Name, Age, etc.) where the FamilyKey in column A matches A2.

If you want to include headers, in cell A4, enter the headers manually:

Name | Age | Gender | Relationship

Or dynamically:

=Sheet1!B1:E1

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin
André Boshoff 20 Reputation points

2025-07-27T11:02:52.7833333+00:00

Hi Marcin

Thank you for the speedy help.

You're spot on about what my People details worksheet contains e.g.

A = FamilyKey

B = Name

C = Age

D = Gender

E = Relationship

I have another sheet which is an input sheet in which I can type the "FamilyKey" (which is unique) to give me info about all the family members with that key value.

The fields that need to be populated by the enquiry are more or less the same for each family member, so what the formula must do is to read all the rows with the manually provided "FamilyKey", then populate the fields e.g. the main family member's name, surname, DoB, gender, age etc. but also for the spouse, child1, child2 etc.

In other words all the fields that need to be populated are dependant on the value of the "FamilyKey" provided. Each field that gets back info from the first sheet must be able to read the "FamilyKey" on the different rows. For each family member the info is always in the same column but in a different row. Therefore the formula must know by the "FamilyKey" how many family members (rows) there are in the Peope detail sheet and then retrieve the same info (column) from each different row for the number of times that there are "FamilyKey" entries. In other words if a family has three members there will be three rows but a family of seven there are seven rows. The formula must be able to:

a. recognise how many rows (family members) there are

b. know from which row the information must be retrieved, so main member is the 1st row, child 2 is the 4th row etc.

Therefore I need one formula that I can use in each field where infor comes back into. It must simply look at the relevant column (probably with VLOOKUP? e.g. Gender) but from the accurate row.

Alternativey I can send you my Excel spreadsheet to look at?

Thanks

André
André Boshoff 20 Reputation points

2025-07-27T11:17:20.04+00:00
André Boshoff 20 Reputation points

2025-07-27T11:19:47.2666667+00:00

first image is the detail of all peope, 2nd image is the input sheet in which each greay cell receives info back based on cell E1 (Familykey). Here you can see that the same data type is populated into ths cell but from difeerent rows. This is what I need help with

Share via

ID all rows with same value in first cell

4 additional answers

Your answer