1

I have a sheet containing color names on several rows with associated numbers in the row directly below them.

In a separate sheet, I want to use a formula to return the color values in a column next to the color names

Sample Data

A B C D
1 red blue green yellow
2 10 10 0 500
3 blue yellow
4 10 500
5 blue green
6 10 0
7 red green
8 10 0

Sample Results

A B
1 red 10
2 blue 10
3 blue 10
4 red 10
5 blue 10
6 yellow 500
7 green 0
8 green 0
9 green 0
10 yellow 500


 

Data in Sheet1
sheet 11

List of Colors in Sheet2
sheet 2

1
  • Dario, it would be helpful if you edit your question to clarify why there are duplicate combinations in the original data and in the results sheet there are also duplicate colors however there isn't a one-to-one relationship and it isn't clear what the order of numbers is. Will a specific color always have the same value? Do you want to list the duplicates? Are you trying to get a count? etc.
    – Blindspots
    Oct 24, 2023 at 15:48

3 Answers 3

1
=WRAPROWS(TOCOL(A:D,1,1),2)
  1. TOCOL stacks the range into a single column:
    • ignore is set to 1 to skip blanks
    • scan_by_column is set to TRUE or 1
  2. WRAPROWS converts the column into 2 column rows by setting the wrap_count to 2.
0
0

Step 1 - Let's get that array of original data into a single column. Since you didn't share the spreadsheet, I made my own with the data in a tab called "Data". All three formulas go in a separate tab ("Solution" in my spreadsheet)

=FILTER({Data!A:A;Data!B:B;Data!C:C}, LEN({Data!A:A;Data!B:B;Data!C:C}))

Step 2 - Pull out the colors into their own column: (This filter will show only the even numbered rows)

=FILTER(A1:A, MOD(ROW(A1:A), 2) = 0)

Step 3 - Pull out the values under the colors in the adjacent column: (This filter will show only the odd numbered rows)

=FILTER(A1:A, MOD(ROW(A1:A), 2) = 1)

You may need to swap the even and odd filters depending on if you have a header row. You could make the first step simpler, but I don't know if you happen to have a ragged array. This formula will work even if you do. Here's my spreadsheet: https://docs.google.com/spreadsheets/d/1Ocbmzv9WCJYpE30bjL7ZQ32C5aj4ve-UD32XW6tGZCs/edit?usp=sharing

0

To get the lowest number under each unique text label above such number, use query() and filter(), like this:

=lambda( 
  data, 
  lambda( 
    colors, numbers, 
    query( 
      { colors, numbers }, 
      "select Col1, min(Col2) 
       group by Col1 
       label min(Col2) '' ", 0 
    ) 
  )( 
    filter(data, istext(data)), 
    filter(data, isnumber(data)) 
  )  
)( 
  { 
    filter( 
      flatten(transpose(A1:D100)), 
      len(flatten(transpose(A1:D100))) 
    ) 
  } 
)

If you are looking for a way to find numbers by their cell fill color, that cannot be done with a formula that uses Google Sheets built-in functions, because none of those functions has access to cell formatting such as fill colors. If that is what you need, you will have to use a script, or devise another way to categorize your data that does not depend on fill colors.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.