Ekazumon
06-04-2019, 09:41 AM
Hello Everyone,
I was looking around the threads to see if I could find a problem similar to mine. Unfortunately, I was unable to find exactly what I was looking for. So, here I am.
Background:
I made an excel sheet to display data by location by using the unique item IDs. I would sort the data extracted from SQL(raw export) to a raw data tab. After several formulas, the item ID is then associated with a particular location (warehouse) as denoted by the column: location. Then, I group up the item IDs via location via sort. I then copy the corresponding item IDs into the matching tab based on the sorted location. I then make each tab a unique workbook and send them to the appropriate individuals.
I have attached a heavily simplified version of the workbook with dummy data.
Issue:
I have been doing this without any problem using my methodology. However, this task is going to be passed down to someone who is technically challenged. I was told by my manger to make this automated as much as possible. Preferably with a vba that can automatically paste the data into the appropriate tabs.
My issue is that I am unaware of how to do a vba copy & paste to another sheet with a condition that is located in another column. My knowledge only extends to if the condition is in the same column that is being copy and pasted. So in this case, I need all Item IDs that are associated with warehouse 1 in the column location to copy and paste into the first column under the tab, warehouse 1.
Alternatively, I successfully created a lookup formula that can auto fill the data. However, it leaves spaces filled with N/A between the header and data if the number of rows is less than number of corresponding Item IDs. Because
of this, I was told that this would not be an appropriate solution even though you would only need to delete the rows with N/A.
Any insight or methodology to solving this problem would be greatly appreciated. Thank you!
I was looking around the threads to see if I could find a problem similar to mine. Unfortunately, I was unable to find exactly what I was looking for. So, here I am.
Background:
I made an excel sheet to display data by location by using the unique item IDs. I would sort the data extracted from SQL(raw export) to a raw data tab. After several formulas, the item ID is then associated with a particular location (warehouse) as denoted by the column: location. Then, I group up the item IDs via location via sort. I then copy the corresponding item IDs into the matching tab based on the sorted location. I then make each tab a unique workbook and send them to the appropriate individuals.
I have attached a heavily simplified version of the workbook with dummy data.
Issue:
I have been doing this without any problem using my methodology. However, this task is going to be passed down to someone who is technically challenged. I was told by my manger to make this automated as much as possible. Preferably with a vba that can automatically paste the data into the appropriate tabs.
My issue is that I am unaware of how to do a vba copy & paste to another sheet with a condition that is located in another column. My knowledge only extends to if the condition is in the same column that is being copy and pasted. So in this case, I need all Item IDs that are associated with warehouse 1 in the column location to copy and paste into the first column under the tab, warehouse 1.
Alternatively, I successfully created a lookup formula that can auto fill the data. However, it leaves spaces filled with N/A between the header and data if the number of rows is less than number of corresponding Item IDs. Because
of this, I was told that this would not be an appropriate solution even though you would only need to delete the rows with N/A.
Any insight or methodology to solving this problem would be greatly appreciated. Thank you!