data cleaning

Hello, I am working on a formula to clean up a list of phone numbers with very inconsistent formats to all include just the 3 digit area code and following 7 digits. I have a formula that removes all spaces and non-numeric characters, but I can't figure out what I need to add that would remove...

Hi, I've cells on two rows which are merged, however, some cells are not merged and have different values/data. E.g. I want to bring Row 14-15 in one row also and delete the empty line and merge the comment section. While I import the table in power query, it shows one row as nil value, so...

Sub DeleteRedundantData() Mylist = Array("Apple", "Bananna", "Pear", "Orange", "Melon") LC = Cells(1, Columns.Count).End(xlToLeft).Column For mycol = LC To 1 Step -1 x = "" On Error Resume Next x = WorksheetFunction.Match(Cells(1, mycol), Mylist, 0) If Not IsNumeric(x) Then...

Hi, I want to solve this in Power Query I have the following data. D8Sr. no.9Product10Qty11Quality12113apple149915average16217oranges184219good20321banana221023low I want to transform this into a proper table GHIJ7Sr. no.ProductQtyQuality81apple99average92oranges42good103banana10low Thanks

Hi, A thanks in advance to whoever replies I have a question in my project for which participants can select multiple answers. The data was obtained through google forms where the file was exported to csv for example for a question it may have answers like following Respondent 1 -...

The messy data looks like the list in columns A & B (see image), the finished list needs to be like columns D&E. I'm cleaning up some very messy data with formulas on the spreadsheet up to this point but the solution could be with VBA also, I'm open to either. So basically the purchase order...

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 I am trying to consolidate about 30 documents into this one master database with Power Query. When expanding all my tables, it seems like the Item column isn't populated for some of the documents. The Occupancy(%) column is organised in a sequence of 5 items for 3 KPIs. > I would like...

Hi, I am using this wholesale customers dataset as an example. Using PQ, I have unpivoted all the columns from Fresh to Delicassen as shown below. What I would like to do next is create a column "Order ID" that would work like an Index column, except it's incrementing by 1 with each new...

Hello....I need some solution on clearing the data with contents along with format... assume I have headers in Row 4 and want to clear the data from 5th row on wards every time when I run the macro....can anyone help me...below code I created but not working ..its clearing the header when I...

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 there everyone, I really need an Excel formula or macro (ideally copy/paste since my VBA is not great) that lets you change the index and lookup array based on the question identifier. So essentially looking at data below, I want my index to change from C2-C4 to C7-C9 based on the identifier...

Dear all, I am trying to generate a material report for different product lines. Unfortunately, when creating the references in our system (different suppliers giving the same product specification on a different part number), the links between specific products was not made in a simple...

Hello, I am exporting some PDFs that I am then importing into Excel to do some data cleaning (removing headers, subtitles, etc.) When I do the export from Bloomberg, I am getting a subfolder for each PDF document. So I would want to be able to run through all the subfolders and do my data...

Hello, I am working on cleaning a PDF file with a header that I don't want to include in my text analysis. Each page begins with the phrase "Page X of Y," followed by 10 more lines of header information. The program I envision would have VBA find each cell that starts with the word "Page."...

Hi All, I have tried various ways and still finding problems so I hope you are able to assist. I currently have a new macro which pulls a text file. In turn adds various points to a leading track. The end points may have 1/2/3/4 different end points and inn turn the leading tracks may also...

I'm putting together some code that will allow me to automate data cleaning. One of the tasks I desire to do is to capture the location of any merged cell and write it to a sheet (for tracking purposes) before I unmerge the cells. The code I have works ok, but it writes a value for each merged...

Hello, Mr. (and Mrs.) Excel! Anyone know of a formula for removing text within multiple sets of parentheses? I would like to take something like this: Compared with placebo, dimethyl fumarate was shown to be effective in the treatment of patients with MS in the phase 3 DEFINE (Gold R et al. N...

Hi All, I have been working with large excel files ranging from 500,000 to 800,000 plus rows of data. Cleaning, matching and merging data is time consuming. I am wondering if you have an good approach of dealing with large data files? Any good data cleaning and matching tools to recommend...

I have several lists of more than 1000 names entered as a single column in Excel 2010 I need to split each string into fields such as FName, LName, MName, Title, Suffix sample data: <tbody> Dr. Bill Lovejoy Dr. Raymond Lutz, Jr. Dr. Nancy Lutz William Macey Paul and Denise Maestas...