How to update Last names to be the same in Sheet1 using Pivot table to detect typos
I have this pivot table.
Row Labels Count of Last Name Teddy Bear 1 TeddyBear 1 CandyCrush 1 Can dy Crush 1 King Bob 1 kingBOB 1
I wanna create a macro whereby it will
Update the Names to be the same automatically
Eg.
I want Teddy Bear(2nd row) to be the same as the (3rd row) [TeddyBear = TeddyBear]
How can I achieve this
Last edited by fluffyvampirekitten; 07-20-2015 at 03:51 AM.
You cannot do that directly on a Pivot Table.
A Pivot only Displays what is there in the underlying data. So you will have to change the underlying Data 1st.
Secondly how do we know which of the 2 is right? Teddy Bear or TeddyBear?
So, what I'd do is, on the underlying data, add a helper column and use the Substitute Function to get rid of all the spaces, which would make both Teddy Bear & TeddyBear to TeddyBear Which I can display on the Pivot Table..
Cheers!
Deep Dave
Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.
Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.
Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!
Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).
What is a helper column?Originally Posted by NeedForExcel
I am afraid you will have to reply to Kyle before I give any further clarification..
Okay noted! Sorry , i missed out the rules .
http://www.mrexcel.com/forum/excel-q...lications.html
Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.
Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
Never use Merged Cells in Excel
Thanks for the tip!
I will absolutely abide by the "cross post" rule in future.
http://www.mrexcel.com/forum/excel-q...lications.html
fluffyvampirekitten, new title ain't any better than old one.
Your post title should accurately and concisely describe your problem.
"Convert this logic" say nothing about your problem.
How about now?
see, you can do it
It would be best to put proper title next time right away so everyone can be more focused on your solution than title.
A helper Column is a column which you create for Temporary Calculations..
Insert a Column between Row Labels & Count of Last Name
Assuming B2 is the cell where you 1st formula will come, try this - =SUBSTITUTE(A2," ","")
Use this new column in the Pivot Table..
Okay ! Noted.see, you can do it
It would be best to put proper title next time right away so everyone can be more focused on your solution than title.
Thanks for the help !
So sorry about those errors>.< Was really desperate to find out the answer.
I can't insert a new column in the Pivot table(between the Row Labels & Count of Last Names)
I go this error :
'We can't make this change for the selected cells because it will affect a PivotTable. Use the field list to change the report .If you are trying to insert or delete cells , move the PivotTable and try again . '
I tried to create a new column in Column A and adjust the formula to be =SUBSTITUTE(B2," ","")
Is it Possible to replace the values in "Row Labels column" cos i couldn't see any effect Count of Last Name Column? ( After updating the names , the count of last names should change to 2)
Hi,
I suggested to add a helper column on the underlying data.
Can you attach a sample workbook? Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Re: How to update Last names to be the same in Sheet1 using Pivot table to detect typos
Please see the sample data
SimpatTest.xlsx
Before:
Pivot Worksheet
Capture1.JPG
Main Worksheet
Capture1a.JPG
After:
Pivot Worksheet
Capture2.JPG
Main Worksheet
Capture2a.JPG
is it possible if i only change the names that are shown in the pivot table ?
I dont want remove all the spaces for all the data , only those that shown in pivot table or the count of last name value is 1.
Last edited by fluffyvampirekitten; 07-21-2015 at 09:56 PM.
Hi,
See the attached file..
yeah looks great!
but is it possible if i only change the names that are shown in the pivot table ?
i dont want to change all?
Pivot Table will show all the Names you have in your source data by default.. Unless of course you manually filter them out..
Oh my. I wanna use macro for that.
Oh well , Thank you so much for the help ^^
Greatly appreciated . Thanks :D
Glad to have helped!
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks