CODEWITHSUNDEEP
excelms-accessvba
James.l.Norman
James.l.Norman

Reputation: 23

Access or Excel best tool for the job

Need some advice, I have a large excel sheet that I need to break up by a value in column B. The kicker is in some cases i need more then one value from column B. Example I might want A and B together and C on its own. I have another workbook that has this mapping. I'm stuck on 1 Best tool for this job and 2 how.

In Excel im not sure how to make this work. I know i'll need a loop to go through the list, but how do i handle the ones that need to be combined?

In access I was thinking a table for the data and a table for my package list. The create a query and loop the values into the query using VBA.

Upvotes: 0

Views: 83

Answers (1)

Srik
Srik

Reputation: 2381

I assume you want to break your main sheet to multiple sheets based on value in column B.

You can make use of excel vba dictionary

Does VBA have Dictionary Structure?

  1. You need to load the workbook that has the mapping into the dictionary as below:

    Key = 'value in column B'

    Value = 'name of the sheet where you want that data to go'

  2. Loop through the sheet containing the data get the value in column B, lookup in the dictionary to get the sheet name to which this data should go. Write the data into that sheet name.

Hope this helps.

Upvotes: 1

Related Questions