Find second occurrence same value in Row

[SeliM]

Good morning,

Thanks for taking time to read my enquiry.

I am assisting an organisation who uses a proprietary software to assess tenders.

I am using 365 to draw required data from an excel export from the proprietary system.

The number of rows and columns vary depending on how many submissions are received.

There are two sets of data side by side (at locations depending on number of submissions which can vary from few (4-5) to many (20-60), the sequence of submitters names on both sets of data is the same - (a,b,c,d - a,b,c,d etc.) on the same row

I can find the first occurrence for "a" and retrieve data however I struggle to find the second occurrence for "a" on the same row. I've tried xlookup without success and wondered if you could assist pls.

Many thanks in advance

Mel

 

[Eric W]

It's hard to tell exactly what your data looks like, but I'd guess it's something like this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Name	Name	Name	Name	Name	Name	Name	Name	Name	Name	Name	Name	Name
2		a	b	c	d			a	b	c	d
3		1	5	9	13			17	21	25	29
4		2	6	10	14			18	22	26	30
5		3	7	11	15			19	23	27	31
6		4	8	12	16			20	24	28	31
7
8
9
10
11	Name	Instance	Rows down	Result
12	a	2	3	19
Sheet7

Cell Formulas
Range	Formula
D12	D12	=INDEX($3:$6,C12,SMALL(FILTER(COLUMN(B2:P2),B2:P2=A12),B12))

The formula in D12 should give you what you're looking for, or at least be a good starting point.

 

[Skyybot]

So your data looks something like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	Age	Height	Weight	Name	Age	Height	Weight	Name	Age	Height	Weight	Name	Age	Height	Weight
2	Jimmy	25	6'7"	175	Jane	25	5'5"	130	Joey	40	6'0"	155	Mike	20	5'9"	200
INPUT

 

[SeliM]

Apologies for not being able to upload SS - have never been able to get the addin to work.

The data structure looks like this:

						Can be many columns wide
ID	Actual Moderated Score		Actual Moderated Weight
Instructions - Group Weight - 0%	Mickey Mouse	Donald Duck	Goofy Pty Ltd	Road Runner	Uncle Sam		Mickey Mouse	Donald Duck	Goofy Pty Ltd	Road Runner	Uncle Sam
3.1​	8.33​	8​	7​	7​	7​		12.49%​	12.00%​	10.50%​	10.50%​	10.50%​
4.1​	8​	8.67​	6.33​	6.67​	6.33​		12.00%​	13.00%​	9.49%​	10.00%​	9.49%​
4.2​	8.33​	8.33​	6.33​	7.33​	7.33​		16.66%​	16.66%​	12.66%​	14.66%​	14.66%​
4.3​	9​	8.33​	7​	6.33​	7​		18.00%​	16.66%​	14.00%​	12.66%​	14.00%​

Objective: To find second Mickey Mouse to retrieve 12.49%, I can't use Index due to worksheet structure which varies according to number of submissions and criteria set.
Row number varies due to number of submissions and whether tender or quotation.
I have been using Match and Offset to find the values of raw data (e.g. 8, 8.67 etc.) however I cannot work out a formula that finds second Mickey Mouse entry to populate a second percentage score table (12.49% etc.).

Hope this explanation of challenge is set out better.
Many thanks.

Mel

 

[Eric W]

This is exactly the layout as I used in post 2, and the formula should work fine.

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ID	Actual Moderated Score		Actual Moderated Weight
2	Instructions - Group Weight - 0%	Mickey Mouse	Donald Duck	Goofy Pty Ltd	Road Runner	Uncle Sam			Mickey Mouse	Donald Duck	Goofy Pty Ltd	Road Runner	Uncle Sam
3	3.1	8.33	8	7	7	7			12.49%	12.00%	10.50%	10.50%	10.50%
4	4.1	8	8.67	6.33	6.67	6.33			12.00%	13.00%	9.49%	10.00%	9.49%
5	4.2	8.33	8.33	6.33	7.33	7.33			16.66%	16.66%	12.66%	14.66%	14.66%
6	4.3	9	8.33	7	6.33	7			18.00%	16.66%	14.00%	12.66%	14.00%
7
8
9
10
11	Name	Instance	Rows down	Result
12	Mickey Mouse	2	1	12.49%
Sheet2

Cell Formulas
Range	Formula
D12	D12	=INDEX($3:$6,C12,SMALL(FILTER(COLUMN(B2:Q2),B2:Q2=A12),B12))