Given the following sample data, I'm attempting to have Google Sheets combine each value of an array in Column A (Years) with a value in Column B (Make) and another in Column C (Model) to arrive at the output shown in Column D (Tags) below:
| Years | Make | Model | Tags |
|---|---|---|---|
| 1973,1974,1975,1976,1977 | Ford | Mustang | 1973 Ford Mustang, 1974 Ford Mustang, 1975 Ford Mustang, 1976 Ford Mustang, 1977 Ford Mustang |
| 1970,1971,1971,1972,1973,1974,1975,1976,1977 | Ford | Mustang | 1970 Ford Mustang, 1971 Ford Mustang, 1971 Ford Mustang, 1972 Ford Mustang, 1973 Ford Mustang, 1974 Ford Mustang, 1975 Ford Mustang, 1976 Ford Mustang, 1977 Ford Mustang |
| Chevrolet | Chevelle | Chevrolet Chevelle | |
| Chevrolet | Chevelle | Chevrolet Chevelle | |
| 1965,1966,1967 | Chevrolet | Chevelle | 1965 Chevrolet Chevelle, 1966 Chevrolet Chevelle, 1967 Chevrolet Chevelle |
| 1967 | Chevrolet | Chevelle | 1967 Chevrolet Chevelle |
| 1968 | Chevrolet | Chevelle | 1968 Chevrolet Chevelle |
| 1968,1969,1971,1971,1972,1973,1974,1975,1976,1977 | Chevrolet | Chevelle | 1968 Chevrolet Chevelle, 1969 Chevrolet Chevelle,1971 Chevrolet Chevelle,1971 Chevrolet Chevelle,1972 Chevrolet Chevelle,1973 Chevrolet Chevelle,1974 Chevrolet Chevelle,1975 Chevrolet Chevelle,1976 Chevrolet Chevelle,1977 Chevrolet Chevelle |
| 1971 | Chevrolet | Chevelle | 1971 Chevrolet Chevelle |
| 1969 | Chevrolet | Chevelle | 1969 Chevrolet Chevelle |
| 1968,1969 | Ford | Mustang | 1968 Ford Mustang, 1969 Ford Mustang |
| 1969,1971 | Ford | Mustang | 1969 Ford Mustang, 1971 Ford Mustang |
| 1971,1972,1973 | Ford | Mustang | 1971 Ford Mustang, 1972 Ford Mustang, 1973 Ford Mustang |
| 1969,1971 | Ford | Mustang | 1969 Ford Mustang, 1971 Ford Mustang |
| 1971,1972,1973 | Ford | Mustang | 1971 Ford Mustang, 1972 Ford Mustang, 1973 Ford Mustang |
| 1983,1984,1985,1986,1987,1988,1989,1990,1991,1992,1993 | Oldsmobile | Cutlass | 1983 Oldsmobile Cutlass, 1984 Oldsmobile Cutlass, 1985 Oldsmobile Cutlass, 1986 Oldsmobile Cutlass, 1987 Oldsmobile Cutlass, 1988 Oldsmobile Cutlass, 1990 Oldsmobile Cutlass, 1991 Oldsmobile Cutlass, 1992 Oldsmobile Cutlass, 1993 Oldsmobile Cutlass |
| 1983,1984,1985,1986,1987,1988,1989 | Oldsmobile | Cutlass | 1983 Oldsmobile Cutlass, 1984 Oldsmobile Cutlass, 1985 Oldsmobile Cutlass, 1986 Oldsmobile Cutlass, 1987 Oldsmobile Cutlass, 1989 Oldsmobile Cutlass |
Despite repeated asks of ChatGPT, Gemini and Copilot for assistance, rephrasing the prompt a number of ways and even providing the sample data above, the formulas they generate always result in errors even though their own sample output looks correct. Any suggestions for how I might get this to work?
=BYROW(
A2:A6,
LAMBDA(r,
ARRAYFORMULA(
TRIM(TEXTJOIN(", ",1,
IFERROR(SPLIT(r,","))&
" "&OFFSET(r,0,1)
&" "&OFFSET(r,0,2))))))
Loop through the year using BYROW and SPLIT the year by , and concatenate(&) the next two columns by OFFSETing from the current row. Finally join back the results using TEXTJOIN.