Joining Multiple excel sheet (Appending data), splitting text, Macro requied

First of all thank you for viewing post.

I am having hard time doing copy paste and creating charts etc. for multiple time with same amount of data.

I request to all of you solve my problem by giving macro code for it.

here is my situation:-

attached are two sheets 1 and 2; I have to generate it through database and each time (150 per day) it come in exact 30 rows but different data with hyperlink and same data formatting. I have to do following things with it.
1. make one excel sheet appended with all excel file generated (150 in this case). with same formatting in sequence.
2. in column F and G present text group should be segregated (separated) in single cell separately. for example if F1 contains (data might vary but format content will remain same)

(A61K9/26
A61K9/50
A61K31/135
A61K31/138
A61K31/19
A61P9/00
A61K)

each word should be cut paste into same row later like L1 = A61K9/26 M1= A61K9/50 and so on..............
Same should be done to G 1 data.
3. last is to create sorting popup menu on top row to quickly see which row has same code (e.g. A61K9/50).

thanks for help if any more query is present please feel free to reply.

Will give this a try and let you know by tomorrow.

I would use MS Access. The joint query can solve your problem more painlessly.

We lost your file during the upgrade. Please attach again.

What about truncating the data? WIll work out something for you shortly.

In the first post, you mentioned that you have 150 files to consolidate. Are these files stored in a folder? I can help you with a code that will open each file and consolidate and then do the truncation. Just provide me the type of file names stored in the folder and i can give you the code immediately.

So only column F should be consolidated from all files and then truncated right? What about columns A to E?

I thought if any one could provide code i will write rest for others but it seems its only working for 'F'. Same data is present in 'G' rest other are not required to be truncated. please write code for 'G' to in similar manner of 'F'. data can be appended after F data but in same column.

below is my next step. if it cannot be done or its take long time please leave it.
additionally if you know how to pool and represent similar truncated text of 'F' and 'G' in bubble chart it will be really rewarding.

i.e. if L1 M2 M5 L7 content is same then it could be pooled to one bubble chart to represent data quantitiy.

Use this code. For now i have taken only column F. If there are more columns, please alter accordingly.

Please Login or Register  to view this content.

So each file will contain data in both F & G columns? So the G data should just be appended below the F data?

yes their might be posibility that G doesnot have any data similar to F (i.e. blank)

data of G should come after F data entery ends could be given cell fill yellow

I didnt understand....where will the data be before truncation?

No no ...what i meant is...will each raw file have the merged data in columns F & G? After consolidating the column F data, can the column G data be added below it?

What error are u getting?

Hope you had a good holiday season.

Which is the line highlighted when you get the error? You will see a yellow line in the code window. You can just copy paste it here so i can help you further.