VBA code to return a list of accounts

K

karunk

New Member
Joined
May 18, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi, I appreciate the help.

I would like a vba code to populate the list of email addresses in column B and additional fields in columns C - H based on the account that is selected in cell c1 from a drop-down list that are located on the tab called "Survey Data Lookup". The column numbers that i want to pull are listed in Row 5 in the screen shot below. I don't necessarily need a button but it would be nice. Also, every month new accounts are added so the list of accounts changes. If its possible to create a new list of accounts (where there are no duplicates, that would remove a step as well but my main ask is to create this list below.

I wrote a formula but it is very slow and causing the workbook to crash. I am looking for a short code that will quickly populate the data more efficiently.

below is a version of the data file that lays out how I have it set up.

I don't write code, although I'd really like to learn. Thanks again for the help.



1652978820072.png
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
Hi and Welcome to MrExcel!

Apparently, you can use an advanced filter. But to adapt it into a macro, you could put a sample of your data from the "Survey Data Lookup" source sheet.
To put a sample of your data, use XL2BB tool minisheet (see my signature). Don't put all the columns, hide the ones you don't need and copy the ones you need (11,18,97,80,81,86,1)

Or if you prefer, you can upload your file to the cloud and once and for all I adapt the macro in your same file.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
 
Upvote 0
DanteAmor said:
Hi and Welcome to MrExcel!

Apparently, you can use an advanced filter. But to adapt it into a macro, you could put a sample of your data from the "Survey Data Lookup" source sheet.
To put a sample of your data, use XL2BB tool minisheet (see my signature). Don't put all the columns, hide the ones you don't need and copy the ones you need (11,18,97,80,81,86,1)

Or if you prefer, you can upload your file to the cloud and once and for all I adapt the macro in your same file.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.
Click to expand...
Hi, thank you for the help. Here is a sample of the data but the complete data set is much larger in terms of rows.

 
Upvote 0
Do you need a macro, you can use this formula
VBA help.xlsx
ABCDEFGH
1Account NameDart Container Corporation<--Select Account
2Report Month/YYY
3No of Survey Results for Account: 15
4
5Column #--->1218.00978081861
6<-- [Click [+] to see additional account names]Customer Email AddressRole in CompanySurvey TypeDivisionSub-DivisionAccount OwnerSurvey Date
7adam.hunt@dart.bizBuyerOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com04/05/2021
8michael.schreiter@dart.bizInfluencerOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com04/05/2021
9greg.perkowski@dart.bizInfluencerStrategicConsumer MillsFood Servicefreddie.bateh@westrock.com04/05/2021
10melissa.esser@dart.bizUserOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com06/05/2021
11janet.howells@dart.bizUserOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com06/05/2021
12timothy.hickey@dart.bizBuyer,User,Decision MakerStrategicConsumer MillsFood Servicefreddie.bateh@westrock.com07/05/2021
13jeff.esser@dart.bizInfluencerOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com07/05/2021
14bill.osburn@dart.bizInfluencerOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com11/05/2021
15justin.cory@dart.bizInfluencerStrategicConsumer MillsFood Servicefreddie.bateh@westrock.com11/05/2021
16
Dashboard
Cell Formulas
RangeFormula
B7:H15B7=LET(f,FILTER(Data!A2:DJ35,Data!BW2:BW35=C1),INDEX(f,SEQUENCE(ROWS(f)),B5:H5))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
C1List=Account
 
Upvote 0
Fluff said:
Do you need a macro, you can use this formula
VBA help.xlsx
ABCDEFGH
1Account NameDart Container Corporation<--Select Account
2Report Month/YYY
3No of Survey Results for Account: 15
4
5Column #--->1218.00978081861
6<-- [Click [+] to see additional account names]Customer Email AddressRole in CompanySurvey TypeDivisionSub-DivisionAccount OwnerSurvey Date
7adam.hunt@dart.bizBuyerOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com04/05/2021
8michael.schreiter@dart.bizInfluencerOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com04/05/2021
9greg.perkowski@dart.bizInfluencerStrategicConsumer MillsFood Servicefreddie.bateh@westrock.com04/05/2021
10melissa.esser@dart.bizUserOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com06/05/2021
11janet.howells@dart.bizUserOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com06/05/2021
12timothy.hickey@dart.bizBuyer,User,Decision MakerStrategicConsumer MillsFood Servicefreddie.bateh@westrock.com07/05/2021
13jeff.esser@dart.bizInfluencerOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com07/05/2021
14bill.osburn@dart.bizInfluencerOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com11/05/2021
15justin.cory@dart.bizInfluencerStrategicConsumer MillsFood Servicefreddie.bateh@westrock.com11/05/2021
16
Dashboard
Cell Formulas
RangeFormula
B7:H15B7=LET(f,FILTER(Data!A2:DJ35,Data!BW2:BW35=C1),INDEX(f,SEQUENCE(ROWS(f)),B5:H5))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
C1List=Account
Click to expand...
Oh, wow. I've never seen this formula before. It worked perfectly. thank you!
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
Fluff said:
Do you need a macro, you can use this formula
VBA help.xlsx
ABCDEFGH
1Account NameDart Container Corporation<--Select Account
2Report Month/YYY
3No of Survey Results for Account: 15
4
5Column #--->1218.00978081861
6<-- [Click [+] to see additional account names]Customer Email AddressRole in CompanySurvey TypeDivisionSub-DivisionAccount OwnerSurvey Date
7adam.hunt@dart.bizBuyerOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com04/05/2021
8michael.schreiter@dart.bizInfluencerOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com04/05/2021
9greg.perkowski@dart.bizInfluencerStrategicConsumer MillsFood Servicefreddie.bateh@westrock.com04/05/2021
10melissa.esser@dart.bizUserOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com06/05/2021
11janet.howells@dart.bizUserOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com06/05/2021
12timothy.hickey@dart.bizBuyer,User,Decision MakerStrategicConsumer MillsFood Servicefreddie.bateh@westrock.com07/05/2021
13jeff.esser@dart.bizInfluencerOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com07/05/2021
14bill.osburn@dart.bizInfluencerOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com11/05/2021
15justin.cory@dart.bizInfluencerStrategicConsumer MillsFood Servicefreddie.bateh@westrock.com11/05/2021
16
Dashboard
Cell Formulas
RangeFormula
B7:H15B7=LET(f,FILTER(Data!A2:DJ35,Data!BW2:BW35=C1),INDEX(f,SEQUENCE(ROWS(f)),B5:H5))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
C1List=Account
Click to expand...
I do have a question tho, its not returning the full email address
Fluff said:
Do you need a macro, you can use this formula
VBA help.xlsx
ABCDEFGH
1Account NameDart Container Corporation<--Select Account
2Report Month/YYY
3No of Survey Results for Account: 15
4
5Column #--->1218.00978081861
6<-- [Click [+] to see additional account names]Customer Email AddressRole in CompanySurvey TypeDivisionSub-DivisionAccount OwnerSurvey Date
7adam.hunt@dart.bizBuyerOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com04/05/2021
8michael.schreiter@dart.bizInfluencerOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com04/05/2021
9greg.perkowski@dart.bizInfluencerStrategicConsumer MillsFood Servicefreddie.bateh@westrock.com04/05/2021
10melissa.esser@dart.bizUserOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com06/05/2021
11janet.howells@dart.bizUserOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com06/05/2021
12timothy.hickey@dart.bizBuyer,User,Decision MakerStrategicConsumer MillsFood Servicefreddie.bateh@westrock.com07/05/2021
13jeff.esser@dart.bizInfluencerOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com07/05/2021
14bill.osburn@dart.bizInfluencerOperationalConsumer MillsFood Servicefreddie.bateh@westrock.com11/05/2021
15justin.cory@dart.bizInfluencerStrategicConsumer MillsFood Servicefreddie.bateh@westrock.com11/05/2021
16
Dashboard
Cell Formulas
RangeFormula
B7:H15B7=LET(f,FILTER(Data!A2:DJ35,Data!BW2:BW35=C1),INDEX(f,SEQUENCE(ROWS(f)),B5:H5))
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
C1List=Account
Click to expand...
Question, how can I use this to return all values but skip blanks and only return results in one column. I would like to pull back the answers provided in Row AA and the example i am using is the Dart company so it should only pull back 3 responses but its not working.
 
Upvote 0
karunk said:
I do have a question tho, its not returning the full email address
Click to expand...
What do you mean?
For the other question use
Excel Formula: 
=FILTER(Data!AA2:AA35,(Data!BW2:BW35=C1)*(Data!AA2:AA35<>""))
Although there are only 2 results not 3
 
Upvote 0
HI Fluff, Thank you so much. This is great help. I love this formula.

Wondering if you can help me again.

I keep finding more things to fix with this dashboard. If the user wanted to selected multiple accounts from the drop down located in cell c1 and have it pull back the results from the data tab, how would that work? the problem is, sometime the account names even though they are the same account, it won't work.

Even though its the same account as stated by the account ID in column BV on the Data Tab, the account reps put them in differently into salesforce differently in column BW which is the account i am using to do the lookup on the dashboard tab. I can use the account ID to pull the information but I can't think of how that would work when I need i can't expect them to know the account ID number. I also want the dropdown arrow to show instead of disappear from the cell c1. Any help would be appreciated.

I've uploaded a new version for reference. I also created a concatenated cell thinking that would be helpful but i don't think it will given we'd still need to be able to select all accounts associated with "coca cola" for example.

drive.google.com

Survey Dashboard May 20220518 v2.xlsm

drive.google.com drive.google.com
 
Upvote 0
As this is a totally different question, it needs a new thread. Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
VBA to append list
Replies
3
Views
162
Cubist
C
M
  • Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
412
MissDB101
M
F
  • Solved
VBA to create list of unique values matching a condition, drawn from a large dataset
Replies
6
Views
212
dermie_72
D
T
  • Question
Excel VBA - Send email in Outlook using 2nd account
Replies
6
Views
409
6StringJazzer
6StringJazzer
S
  • Question
Can a macro help create a user hierarchy?
Replies
7
Views
146
Akuini
Akuini

Forum statistics

Threads
1,214,411
Messages
6,119,360
Members
448,888
Latest member
Arle8907

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top