Formula Help - Replicate each Cell down muliple rows

[Queenofmycastle]

Sheet 1
A B
1 Red 2
2 Blue 3

Sheet 2
A
1 =Sheet1!A2
2 =IFERROR(IF(COUNTIF(A$2:A2,A2)=VLOOKUP(A2, Sheet1!$A$2:$B$3,2,0), INDEX(Sheet1!$A$2:$A$3,SUMPRODUCT(1/COUNTIF(Sheet2!A$2:A2,Sheet2!A$2:A2)) + 1, 1), Sheet2!A2)," ")

Sheet2 Results
Sheet 1
A
1 Red
2 Red
3 Blue
4 Blue
5 Blue

The above formula works perfectly except that the example only included 2 rows of data, I want it to be able to continue if I have 10 colors or even 100 colors. I can't figure out where the limitation Help is appreciated!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Queenofmycastle]

I will do that! We are using Office 16

 

[Fluff]

Thanks for that.
How about

=IFERROR(IF(COUNTIF(A$2:A2,A2)=VLOOKUP(A2, Sheet1!$A$2:$B$100,2,0), IF(INDEX(Sheet1!$A$2:$A$100,SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)) + 1, 1)="","",INDEX(Sheet1!$A$2:$A$100,SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)) + 1, 1)), A2),"")

 

[Queenofmycastle]

=IFERROR(IF(COUNTIF(A$2:A2,A2)=VLOOKUP(A2, Sheet1!$A$2:$B$100,2,0), IF(INDEX(Sheet1!$A$2:$A$100,SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)) + 1, 1)="","",INDEX(Sheet1!$A$2:$A$100,SUMPRODUCT(1/COUNTIF(A$2:A2,A$2:A2)) + 1, 1)), A2),"")

You nailed it! So.... Our IT person was just here and she stated that we are using Home and Business 2021 but I found Office 16 when I clicked on Excel Properties. Am I crazy?

 

[Fluff]

If you have 2021, then you could use this in A2 only (clear column A first)

=LET(r,ROWS(Sheet1!B2:B100),a,MMULT(--(SEQUENCE(r)>=SEQUENCE(,r)),--Sheet1!B2:B100),XLOOKUP(SEQUENCE(MAX(a)),a,Sheet1!A2:A100,,1))

 

[Queenofmycastle]

=LET(r,ROWS(Sheet1!B2:B100),a,MMULT(--(SEQUENCE(r)>=SEQUENCE(,r)),--Sheet1!B2:B100),XLOOKUP(SEQUENCE(MAX(a)),a,Sheet1!A2:A100,,1))

It worked so I must have 2021! I learn something new everyday!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Fluff]

It worked so I must have 2021!

In that case I suggest you update your profile to show that.

Glad to help & thanks for the feedback.