Help needed regarding presentation of data

excel_1317

Board Regular
Joined
Jun 28, 2010
Messages
212
I have a large data set from our CRM. Different people create contacts in it. So most compulsory fields are left blank or incorrect info. is added. Below is the sample data with the names of the people who created those contacts. There are around 35 people who created these. Now I want to compile a single spreadsheet and send to all these 35 people. First i thought of creating 35 tabs with names of the people who created these contacts and ask them to fill in the blank/incorrect fields. Cells with no color are filled with correct info and Red cells are the ones which need to be corrected. Please suggest how to do achieve this in professional manner without creating 35 tabs. THANKS IN ADVANCE...!!

Here is the sample data... Columns from B till P(red color) needs to be corrected..

Sheet4
ABCDEFGHIJKLMNOP
1Contact IDCreated ByAccount NameFirst NameLast NameDepartmentField of business activityJob TitleGeneric job title-Corporate/Law firms (whichever is applicable)EmailContact Address same like Account?Mailing streetMailing townMailing State/countyMailing Zip/Postal CodeMailing Country
20032000000tzB9pDavidIBA GroupBlankPatrickBlankBlankBlank
30032000000tz8TdDavidFady Sarkis Law OfficeFadySarkisBlankBlankBlankBlankBlankBlank
40032000000tyOB4DavidUAE UniversityManfredMalzahnBlankBlankBlankBlankBlankBlankBlankBlankBlankBlankBlank
50032000000tyif4DavidNony Et AssociesJeromeSartoriusBlank
60032000000tyRBIDavidStrulik SaBCarneiroBlank
70032000000tyiILDavidJuris NearshoreDanielAbitbolBlankBlankWrong info
80032000000tyQGgDavidImmunoclinEmericReynaudincomplete infoBlankBlank
90032000000tyNjyDavidUAE UniversityMohammedHussien Saleh Al-HemairyBlankBlankBlankBlankBlankBlank
100032000000txx7aJoeThx LtdErikRieglerBlankBlankBlankBlankwrong email id
110032000000ty4hpJoeFlipboardDeemaTamimiBlankBlankBlank
120032000000tyc0xJoeQuan & AssociatesNancyQuanBlankBlankBlankBlankBlankBlankBlank
130032000000tvqnxJoeRichard SheedyRichardSheedyBlankBlankBlankBlankBlankBlank
140032000000tzPwYJoeFive Apes, Inc.PatrickChiangBlankBlankBlankBlank
150032000000tyjCpJoePrysm IncPeterUllmannBlankBlankBlankBlankBlankBlankBlankBlankBlank
160032000000tybitJoeBlue Sands IPAlanSorgiBlankBlankBlankBlankBlankBlankBlankBlankBlank
170032000000tyEmEJoeVenti Group LLCTonyEichenlaubBlankBlankBlankBlankBlankBlankBlankBlankBlank
180032000000txxUrJoeThe Cheesecake Factory IncChristineVuBlankBlankBlankBlankBlankBlankBlankBlankBlank
190032000000tvrqTMariaCovington & Burling LLPMaryAnnHongBlankBlankBlank
200032000000tyqbuMariaBaker & Hostetler LLPKellyKurtzBlankBlankBlankBlankBlankBlankBlankBlankBlankBlank
210032000000tyF0EMariaELLUCIANBlankWrong infoBlankBlankBlankBlankBlankBlankBlankBlankBlank
220032000000twZWWMariaBlank Rome LLPJamieBerthaBlankBlankBlankBlankBlankBlankBlankBlankBlank
230032000000twSFKMariaFaegre Baker DanielsMickiePotterBlankBlankBlankBlank
240032000000twSEqMariaFaegre Baker DanielsMichelleDavisBlankBlankBlankBlankBlankBlankBlankBlankBlank

<tbody>
</tbody>
Excel 2007
 
Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
If the goal is to get the job done in the quickest way possible, implement Kyle's AutoFilter suggestion and send it out. It seems that you don't need the data back from the users, as they need to go into your CRM to make the changes, am I right? You will probably go into the CRM and pull this sheet again later to see who made their repairs?

If the goal is to do it in a more complex way than required as a learning opportunity, then lots of other options come to mind. Do you know how to do macros? You could gather a list of everybody's system usernames and build a macro which will determine who is opening the sheet, then hide any row not relevant to that user. That way when David opens the file, it hides all rows that don't pertain to David, etc. Note that I used SYSTEM usernames, not EXCEL usernames. Lots of people never change the Excel username from whatever the corporate IT people defaulted in there, so that won't work.
 
Upvote 0
Wizz, could you please let me know the details on how to transform very fast a table like a pivot back to a list, etc.

Hi,
I have an ideea for you.
1.you quckly transform your table in a list like this below:

ContactID Account Name Column to be filled Reason
0032000000tzB9p IBA Group First Name Blank
0032000000tzB9p IBA Group Mailing street B lank
0032000000tzB9p IBA Group Mailing State/county Blank
0032000000tyiIL Juris Nearshore Mailing street Blank
0032000000tyiIL Juris Nearshore Mailing State/county Wrong info
0032000000tyQGg Immunoclin Job Title incomplete info
0032000000tyQGg Immunoclin Mailing street Blank

2. Send this worksheet to everyone to fill their own cells in column D
3. when you receive back the file, you can use it in a vlookup formula in your initial table and in some minutes it is filled.

If you like the ideea, I will give you more details (how to transform very fast a table like a pivot back to a list, etc.)
 
Upvote 0
Hi,
here is a link with print screens for transforming a table like pivot in list.
http://j-walk.com/ss/excel/usertips/tip068.htm
one more info: if you use excel 2007, for pivot table press Alt-D -P in order to have the option "consolidation tip"

I will think about your idea to click the name and show the list of info to be updated. (this needs VBA)
 
Upvote 0
Thanks wizz, Please let me know if you get any success on VBA coding...

Hi,
here is a link with print screens for transforming a table like pivot in list.
http://j-walk.com/ss/excel/usertips/tip068.htm
one more info: if you use excel 2007, for pivot table press Alt-D -P in order to have the option "consolidation tip"

I will think about your idea to click the name and show the list of info to be updated. (this needs VBA)
 
Upvote 0
Hi Karan,

you can do one thing on this. we can create a main sheet with login credentials and it will have the drop down and your customer need to select their name and the password then it will show only his particular sheet.

then you need to create superate sheets for every (35) customer or clients.

Regards,
Satish.
 
Upvote 0
Hi,
I made an example excel file with two sheets.
I don't know how to attach the excel file, so I will just explain it.


Sheet "Namelist" has the list with all account names.
Sheet "Data" has the fields to be filled by everyone.


The vba code does this: when you doubleclick on an account name of the list from "NameList",
the sheet "Data" became activated and then makes auto filter with that account name.
In this way each person doubleclick on his account name and than he sees only his fields.




all the code you need is this:


Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Dim AccName As String
     If Target.Value <> "" Then
            AccName = Target.Value
            Sheets("Data").Activate
            ActiveSheet.Range("$A$1:$D$65000").AutoFilter Field:=2, Criteria1:=AccName
     End If
End Sub




Give a try.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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