Hi All,
First time poster and am really stuck on getting a solution for the following:
I have an excel sheet that needs exporting into MYSQL. This sheet contains a grid with the rows being components and columns being products.
Column A holds the component name with columns b onwards using row 1 as product names.
The grid is a grid of components that make up that product in row 1 of the column. not all components are used in a product, for example:
<code style="margin: 0px; padding: 1px 5px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: pre-wrap; background: rgb(238, 238, 238);">xxxxxxxxxxx | product 1 | product 2
component 1 | 1 | 0
component 2 | 0 | 1
component 3 | 0 | 10
</code>and so on. I am trying to get a formula that can check each column against each row, if the column has a value > 0 or not blank then grab the product name, component name and the value ready to be used to generate SQL (i already have component ids etc)
is this possible? I have seen reverse lookups and lookups using grids but not to this level..
would be very grateful to any excel wizards out there that can save me hours of manually doing this (there are 102 products with ~ 1000 different components..)
I have no idea where to start but all the grid lookups i have seen give you the row/column value only not everything..
First time poster and am really stuck on getting a solution for the following:
I have an excel sheet that needs exporting into MYSQL. This sheet contains a grid with the rows being components and columns being products.
Column A holds the component name with columns b onwards using row 1 as product names.
The grid is a grid of components that make up that product in row 1 of the column. not all components are used in a product, for example:
<code style="margin: 0px; padding: 1px 5px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: pre-wrap; background: rgb(238, 238, 238);">xxxxxxxxxxx | product 1 | product 2
component 1 | 1 | 0
component 2 | 0 | 1
component 3 | 0 | 10
</code>and so on. I am trying to get a formula that can check each column against each row, if the column has a value > 0 or not blank then grab the product name, component name and the value ready to be used to generate SQL (i already have component ids etc)
is this possible? I have seen reverse lookups and lookups using grids but not to this level..
would be very grateful to any excel wizards out there that can save me hours of manually doing this (there are 102 products with ~ 1000 different components..)
I have no idea where to start but all the grid lookups i have seen give you the row/column value only not everything..
Last edited:
