Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-09-2017, 06:59 AM
doctormathis doctormathis is offline Cleaning up a database Windows 10 Cleaning up a database Office 2016
Novice
Cleaning up a database
 
Join Date: Aug 2017
Posts: 5
doctormathis is on a distinguished road
Default Cleaning up a database

Hello,

I am looking for some help in cleaning up some ones poor decisions. You see previously it was decided to have multiple inputs into a single cell. The example below shows all of the inputs in a single cell. IE

A
MSF 04/28/16: Received signature. Notified LMR to review.
MSF 04/27/16: Sent follow-up email for signature
1. MSF 03/22/16: Received certificate. Confirmed account is current. Need additional signature. Sent email to SC


MSF 03/15/16: Sent 1st email

Now I need to find a way to remove each input and leave only one in each cell. IE

A B C
1 MSF 04/28/16 Received Signature. Notified LMR for review.
2 MSF 04/27/16 Sent follow-up email for signature


I originally thought I could do this with an if than statement but not sure. So now I am asking you the excel gurus to help with finding a way to splice data and not having to manually edit each one which is 7000+ rows.
Reply With Quote
  #2  
Old 08-09-2017, 07:10 AM
NoSparks NoSparks is offline Cleaning up a database Windows 7 64bit Cleaning up a database Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

Can you attach a sample workbook with a few examples (personal info replaced with generic) of what you're facing and the outcome you're after so we know exactly what you're dealing with ?
Reply With Quote
  #3  
Old 08-09-2017, 07:37 AM
doctormathis doctormathis is offline Cleaning up a database Windows 10 Cleaning up a database Office 2016
Novice
Cleaning up a database
 
Join Date: Aug 2017
Posts: 5
doctormathis is on a distinguished road
Default Example

Here is the example.

I have labeled the tabs "Start Point" and "Example of end Product"

As you can see from the Notes column on the Start Point tab we have three instances. This is known by the initials MSF. MSF is the user ID that inputted the data originally. The initials are then followed by a date and then terminates with the note section.

The question is how do I splice the initials, date, and notes from the original "Notes" Column into their own column and row. Each instance needs its own row.
Attached Files
File Type: xlsx example.xlsx (11.3 KB, 9 views)
Reply With Quote
  #4  
Old 08-09-2017, 08:52 AM
NoSparks NoSparks is offline Cleaning up a database Windows 7 64bit Cleaning up a database Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

Okay, I'll work on this, it will require macros so hope you are not limited to only using .xlsx files.

The results for the first set of notes should really have been 4 rows, right ?
Reply With Quote
  #5  
Old 08-09-2017, 09:09 AM
doctormathis doctormathis is offline Cleaning up a database Windows 10 Cleaning up a database Office 2016
Novice
Cleaning up a database
 
Join Date: Aug 2017
Posts: 5
doctormathis is on a distinguished road
Default

Quote:
Originally Posted by NoSparks View Post
Okay, I'll work on this, it will require macros so hope you are not limited to only using .xlsx files.

The results for the first set of notes should really have been 4 rows, right ?

Yes it should have. Thank you for your help!
Reply With Quote
  #6  
Old 08-09-2017, 10:46 AM
NoSparks NoSparks is offline Cleaning up a database Windows 7 64bit Cleaning up a database Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

hello doc

I have no problem getting things to individual rows but am having issues getting the userid and dates due to inconsistencies of the data.

Why do the date formats differ ie: C2 and C5, is this normal ? How is the original data obtained ?
Do you have a list of all userid's that could be checked for ?
Will the userid always be at the start of the line when used ?
Reply With Quote
  #7  
Old 08-09-2017, 12:34 PM
doctormathis doctormathis is offline Cleaning up a database Windows 10 Cleaning up a database Office 2016
Novice
Cleaning up a database
 
Join Date: Aug 2017
Posts: 5
doctormathis is on a distinguished road
Default

Quote:
Originally Posted by NoSparks View Post
hello doc

I have no problem getting things to individual rows but am having issues getting the userid and dates due to inconsistencies of the data.

Why do the date formats differ ie: C2 and C5, is this normal ? How is the original data obtained ?
Do you have a list of all userid's that could be checked for ?
Will the userid always be at the start of the line when used ?

Yes, previously the current users had no set guidelines for inputs. I am thinking that if I group each section of issues like the one you noticed I will have to come up with an answer for each group.
Reply With Quote
  #8  
Old 08-09-2017, 01:51 PM
NoSparks NoSparks is offline Cleaning up a database Windows 7 64bit Cleaning up a database Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

A user form for data entry would allow you to control (future) data entry but that doesn't help much with what you've already got.

Have a look at this, I've assumed the first thing on each line will be the userid and it will always be 3 characters long then next will be the entered date, but if the date is first there will be no userid.
Let me know if this will be of use.
Good luck.
Attached Files
File Type: xlsm DoctorMathis_example.xlsm (27.3 KB, 20 views)
Reply With Quote
  #9  
Old 08-10-2017, 06:20 AM
doctormathis doctormathis is offline Cleaning up a database Windows 10 Cleaning up a database Office 2016
Novice
Cleaning up a database
 
Join Date: Aug 2017
Posts: 5
doctormathis is on a distinguished road
Default

I'd say this is pretty much solved. I can build on your work NoSparks! thank you!
Reply With Quote
  #10  
Old 08-10-2017, 06:58 AM
NoSparks NoSparks is offline Cleaning up a database Windows 7 64bit Cleaning up a database Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

You're welcome, glad I could assist.
Reply With Quote
Reply

Tags
baddecisions, expert, splice



Similar Threads
Thread Thread Starter Forum Replies Last Post
Cleaning then managing inbox ash_scotland88 Outlook 0 11-28-2016 06:04 AM
Cleaning up a database Cleaning up Text Pasted from Websites, E-mails, PDFs etc. macropod Word 0 02-06-2016 02:09 PM
Cleaning up a database Unable to download Microsoft Office 2010 after cleaning system Warxfreedom Office 3 01-15-2016 11:26 PM
Update database Sandhya Excel 1 03-04-2015 07:55 AM
Cleaning up a database little database gsrikanth Excel 3 06-29-2012 05:23 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:33 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft