Microsoft Office Forums Assigning numbers to every group in a set of data
 Register FAQ Search Today's Posts Mark Forums Read

#1
03-27-2021, 02:45 AM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 364
Assigning numbers to every group in a set of data

Hi. I have a list of names that need to be grouped and numbered. I have a problem with the formula that assigns the number in each group where the names are not listed in consecutive rows.
I have attached a sample sheet.

Thank you.
Attached Files
 NameCoding.xlsx (26.5 KB, 6 views)
#2
03-27-2021, 08:45 AM
 Purfleet Windows 10 Office 2019 Expert Join Date: Jun 2020 Location: Essex Posts: 263

Match kind of works for the name code as it will return the first match

=MATCH([@Name2],[Name2],0)

And for the name try

=IF([@Name3]<>"N","",INDEX([Name],MATCH([@Name3]&[@GroupName],[Name3]&[GroupName],0)))

Added extra columns to sheet tNames (2)
Attached Files
 NameCoding_Purfleet.xlsx (36.5 KB, 2 views)
#3
03-27-2021, 04:29 PM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 364

Thank you Purfleet.

The MATCH formula must return 1, 2, 3, 4, 5, 6, 7, 8...... It left out numbers 2,3, 6, 17 and 18.

The formula IN THE GroupNameX must populate all the rows with the chosen name for each group by Name3.
#4
03-29-2021, 01:41 PM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 478

You have the UNIQUE function available? In cell E2:
=MATCH([@Name2],UNIQUE([Name2]),0)
copied down.
#5
03-29-2021, 02:12 PM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 478

I think Purfleet's solution will work for you if you use his
=MATCH([@Name2],[Name2],0)
in E2 (copy down if it doesn't happen automatically).
Leave your VLOOKUP as it is in column G.

The numbers don't have to be contiguous values for this.

Without the qUnique table, try in column G
=INDEX([Name],MATCH([@NameCode]&"N",[NameCode]&[Name3],0))
#6
03-30-2021, 12:37 AM
 Marcia Windows 7 32bit Office 2013 Expert Join Date: May 2018 Location: Philippines Posts: 364

Thank you p45Cal. I adopted the Unique formula because it is not skipping some numbers.

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Editor Word 3 05-26-2019 07:12 AM jelkin1 Excel 1 12-10-2015 06:12 AM mediaset23 Visio 0 07-31-2015 04:18 AM Jack-P-Winner Excel 0 11-25-2013 10:55 PM djcozens Project 1 07-09-2012 05:44 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 06:40 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top