data cleansing

Hi So I have a bit of a data cleansing problem for work. I have approximately 20 workbooks all containing approx 25 sheets and I've been asked to combine all of this data into one master sheet. The problem is that whilst all of the sheets have some of the data that we need, the column...

Hi all, I am trying to automate a data cleansing and couple of Lookup activities in my weekly report by recording macros. The problem is the weekly raw data workbooks keep changes based on the week or other criteria. I was wondering if there is a way to solve this problem? I tried to copy and...

I'm trying to use Power Query to clean up some data and I've hit a road block. My knowledge in power query is very limited so I'm hoping someone with more experience can help me out. I've attached a link with screenshots of the data model (original.png) and the desired outcome (desired.png) I...

Hi all, Hoping to find some help on this one! I have a list of thousands of addresses containing numbers and street names, and I'm trying to "shave off" the number (and sometimes, unit name - e.g. 15/A or 64C) from this list in order to isolate the street name. Unfortunately there are no clear...

Hi all, I'm looking to execute a find & replace on a dataset whereby anything containing the value 'xxx' is replaced with the value 'xxx'. For example, in a set of: yyyxxxyyy hhhxxxhhh xxxhhhhhh wwwaaaxxx xhhaxxghh After the find & replace, I would like the cells to read: xxx xxx xxx xxx...

How do I remove characters around the date in a cell for eg, íf someone stupid has put in a cell ' exp 10/10/2014 year to go' - i want to remove all surrounding text or number (all gibberish) so it is just the data i.e. '10/10/2014' in the cell so i can analyse it. For work, please help soon...

I have a large data of families with the information about age, sex, marital status etc. of each member in the columns. The marital status column has somehow been inverted for each family during extraction. Let me explain this with an example. Following is the format of my data <table...

Hi Everybody, I am supposed to do client analysis, howevere, I have a big problem with data quality starting with client's names :( I am supposed to match our key accounts for the largest companies in our region. The official list contains full official names of the companies, e.g. John...

Hi guys, Is it possible to upload a spreadsheet of badly formatted addresses and use google maps to autocorrect them to their proper addresses and address form? Thanks in advance,