consolidate values in each record according to a specific structure

Hello,

Interesting project for the VBA guru! I have an excel file that is extracted from a database with a structure that stays consistent throughout the excel file. Please open the example workbook, and while reading this post you can view the example and see what I am talking about. If you don't open the file, you will most likely not know what I am trying to describe as it can be confusing.

Column H is the output of what I need the VBA code to do with the giving columns (A:G). The extracted file from the database will include a similar structure to what I have provided in columns A:G. However, the actual extract does have the same structure, but goes through column J. It is possible to have multiple categories in the same column that should have the EXACT same structure in column H.

Ideally, I would like to have the output (that is currently in column H), show up in column K as the actual data set I will use with this code goes from column A to column J.

Please let me know if you have any questions as to what I need. Thank you!

Hey tony,

I apologize if I wasn't clear. Text to columns will not work in my case as column H is not part of the database extract. I have reformatted the example workbook with some colors:

Yellow: Database extract
Green: Output I need the VBA to do with the database extract

Explanation of Database Extract:
*column A (League) --> This value will be the first value in column H that is before the first "/"
*column B (Team#) --> This value will be the second value in column H AFTER the first "/"
*column C (Position#) --> This value will be the third value in column H AFTER the SECOND "/"
...
...
This order stays consistent. However, please take notice that if Team # changes then the strings below the Team# is part of the team structure. Every time the Team# changes, then the strings below the Team# will be paired with this particular Team#. The organizational breakdown structure is consistent with the change. I hope this clears any confusion. If it doesn't and you need more clear descriptions as to what the VBA code needs to accomplish, please let me know. Thank you for all of your help with this!

Hey Tony,

Thank you for your help on this as it is much appreciated! Again, I do apologize if I haven't explained enough of the specifics in order for you to approach how to write the VBA coding. Please see the answers to your questions below:

1) How the sheet is populated:
- I export a query from a database which results in an excel file similar to the yellow highlighted columns.

2) VBA should run:
- The VBA will be executed by someone clicking a button to execute the procedure

3) Number of records in database extract:
- The number of records in the database extract will always vary. As of right now, the file goes to about 3,000 records (of which are all in the same format I provided, however the number of columns in the actual database extract is from A:J, whereas the file I provided goes from A:G --> However, the format of the merged cells with subcategories is the same format I have provided)

I hope this helps. If it doesn't please don't hesitate to ask any more questions! Thank you again

Hey Tony,

Did the previous explanation help?

Maybe:

Please Login or Register  to view this content.

Maybe:

Please Login or Register  to view this content.