Looking for help in the following:
Trying to move data in multiple rows in tab "Colors" to one row on "Summary" tab.
Colors tab looks like this:
A B C D
1 Red 1 2
2 Red 10 11 15
3 Red 23 150 256
4 Blue 5 6 7
5 Blue 1 11 15
6 Green 23
I have 3000 Colors in column A on the Colors tab.
I would like the Summary tab to look like this, pulling all the data from the Colors tab:
A B C D E F G H
1 Red 1 2 10 11 15 23 150 256
2 Blue 5 6 7 1 11 15
3 Green 23
I have been able to get the first row for each color using the formula below, but I need to modify it so that when it gets to the end of the row (blank), it goes to the next row.
{=INDEX(Colors!$A$1:$AV$23851,SMALL(IF(Colors!$A:$A='Summary'!$A1,ROW(Colors!$A:$A)),ROW(1:1)),D$2)}
I have D$2 referencing a column number, but that may be incorrect if the formula is modified.
Any help is much appreciated!
Thank you in advance.
Trying to move data in multiple rows in tab "Colors" to one row on "Summary" tab.
Colors tab looks like this:
A B C D
1 Red 1 2
2 Red 10 11 15
3 Red 23 150 256
4 Blue 5 6 7
5 Blue 1 11 15
6 Green 23
I have 3000 Colors in column A on the Colors tab.
I would like the Summary tab to look like this, pulling all the data from the Colors tab:
A B C D E F G H
1 Red 1 2 10 11 15 23 150 256
2 Blue 5 6 7 1 11 15
3 Green 23
I have been able to get the first row for each color using the formula below, but I need to modify it so that when it gets to the end of the row (blank), it goes to the next row.
{=INDEX(Colors!$A$1:$AV$23851,SMALL(IF(Colors!$A:$A='Summary'!$A1,ROW(Colors!$A:$A)),ROW(1:1)),D$2)}
I have D$2 referencing a column number, but that may be incorrect if the formula is modified.
Any help is much appreciated!
Thank you in advance.