I'm usually pretty good at googling what I need done in excel but here I am stuck for even what to google. Here's what I need.

I'm trying to import into a database a range of fitting types. Here is the google sheets version.

https://docs.google.com/spreadsheets/d/1DwrkzfNPjWKUePEsXkJeU0t6Tuy-gyDBgi1u0525dfM/edit?usp=sharing

Basically, for every type, I need it to have a row of every other option.

Ex

The first rows should be 180 Degree * Every Material * Unit of Measure *Type of Connection (I can leave out the connection size)

180 Degree | Aluminium | mm | m-45-Degree Flare
180 Degree | Aluminium | IN | m-45-Degree Flare

Hopefully, this makes sense.


Is there a way to make excel build this from the table I already have?

Mattressburn said:

---

I'm usually pretty good at googling what I need done in excel but here I am stuck for even what to google. Here's what I need.

I'm trying to import into a database a range of fitting types. Here is the google sheets version.

https://docs.google.com/spreadsheets/d/1DwrkzfNPjWKUePEsXkJeU0t6Tuy-gyDBgi1u0525dfM/edit?usp=sharing

Basically, for every type, I need it to have a row of every other option.

Ex

The first rows should be 180 Degree * Every Material * Unit of Measure *Type of Connection (I can leave out the connection size)

180 Degree | Aluminium | mm | m-45-Degree Flare
180 Degree | Aluminium | IN | m-45-Degree Flare

Hopefully, this makes sense.


Is there a way to make excel build this from the table I already have?

---

How frequently will this need to be updated?

If you need that concatenated string in a cell, you can create all of your separate combinations in rows and columns like i did on the second sheet, and then use"

=A1&" | "&B1&" | "&C1&" | "&D1
to put them all together into one string like you showed.

TMoney2007 said:

---

Mattressburn said:

---

I'm usually pretty good at googling what I need done in excel but here I am stuck for even what to google. Here's what I need.

I'm trying to import into a database a range of fitting types. Here is the google sheets version.

https://docs.google.com/spreadsheets/d/1DwrkzfNPjWKUePEsXkJeU0t6Tuy-gyDBgi1u0525dfM/edit?usp=sharing

Basically, for every type, I need it to have a row of every other option.

Ex

The first rows should be 180 Degree * Every Material * Unit of Measure *Type of Connection (I can leave out the connection size)

180 Degree | Aluminium | mm | m-45-Degree Flare
180 Degree | Aluminium | IN | m-45-Degree Flare

Hopefully, this makes sense.


Is there a way to make excel build this from the table I already have?

---

How frequently will this need to be updated?

If you need that concatenated string in a cell, you can create all of your separate combinations in rows and columns like i did on the second sheet, and then use"

=A1&" | "&B1&" | "&C1&" | "&D1
to put them all together into one string like you showed.

---

Very rarely. Going into a meeting right now but will check on this later. Thanks for the quick response.

If you're using excel you could also just set up a query in ms query and not join the columns logically and it will give you each permutation.

TMoney2007 said:

---

Mattressburn said:

---

I'm usually pretty good at googling what I need done in excel but here I am stuck for even what to google. Here's what I need.

I'm trying to import into a database a range of fitting types. Here is the google sheets version.

https://docs.google.com/spreadsheets/d/1DwrkzfNPjWKUePEsXkJeU0t6Tuy-gyDBgi1u0525dfM/edit?usp=sharing

Basically, for every type, I need it to have a row of every other option.

Ex

The first rows should be 180 Degree * Every Material * Unit of Measure *Type of Connection (I can leave out the connection size)

180 Degree | Aluminium | mm | m-45-Degree Flare
180 Degree | Aluminium | IN | m-45-Degree Flare

Hopefully, this makes sense.


Is there a way to make excel build this from the table I already have?

---

How frequently will this need to be updated?

If you need that concatenated string in a cell, you can create all of your separate combinations in rows and columns like i did on the second sheet, and then use"

=A1&" | "&B1&" | "&C1&" | "&D1
to put them all together into one string like you showed.

---

a join (Google Sheets) or textjoin (Excel) would be easier.

Google
=JOIN(" | ", A1:E1)

Excel (true ignores empty cells, false includes them)
=TEXTJOIN(" | ", false, A1:E1)

If that's what you're going for. You can use multiple arrays in both Google and Excel if there are pieces of the data you don't want to include (example: =TEXTJOIN(" | ", false, A1:C1, E1:G1)

edit: had to change D1 to E1 because forum code was forcing emojis

BEaggie08 said:

---

TMoney2007 said:

---

Mattressburn said:

---

I'm usually pretty good at googling what I need done in excel but here I am stuck for even what to google. Here's what I need.

I'm trying to import into a database a range of fitting types. Here is the google sheets version.

https://docs.google.com/spreadsheets/d/1DwrkzfNPjWKUePEsXkJeU0t6Tuy-gyDBgi1u0525dfM/edit?usp=sharing

Basically, for every type, I need it to have a row of every other option.

Ex

The first rows should be 180 Degree * Every Material * Unit of Measure *Type of Connection (I can leave out the connection size)

180 Degree | Aluminium | mm | m-45-Degree Flare
180 Degree | Aluminium | IN | m-45-Degree Flare

Hopefully, this makes sense.


Is there a way to make excel build this from the table I already have?

---

How frequently will this need to be updated?

If you need that concatenated string in a cell, you can create all of your separate combinations in rows and columns like i did on the second sheet, and then use"

=A1&" | "&B1&" | "&C1&" | "&D1
to put them all together into one string like you showed.

---

a join (Google Sheets) or textjoin (Excel) would be easier.

Google
=JOIN(" | ", A1:E1)

Excel (true ignores empty cells, false includes them)
=TEXTJOIN(" | ", false, A1:E1)

If that's what you're going for. You can use multiple arrays in both Google and Excel if there are pieces of the data you don't want to include (example: =TEXTJOIN(" | ", false, A1:C1, E1:G1)

edit: had to change D1 to E1 because forum code was forcing emojis

---

That's good to know. I haven't had to do it often enough to learn a better way.

Note that textjoin is a new function. Office 365 and excel 2019 only.

I think building a query would be the easiest way to do this.

I would paste the table into a text file awk out the columns and paste them in excel. But I'm an old Unix/Linux, vi, tcsh knucklehead who doesn't like to waste time. I've always got a Linux vpn window open.