+ Reply to Thread
Results 1 to 7 of 7

Data clean up

  1. #1
    Registered User
    Join Date
    05-30-2022
    Location
    Mol, Belgium
    MS-Off Ver
    Professional Plus 2016
    Posts
    3

    Data clean up

    Hello,

    I need help with cleanup of my data. I am using an excel 2016 version, so filter formula does not work.

    I need a formula to which removes data that is below 1% of highest data (intensity) and gives the rest of the data in the new sheet. I tried to do this with IF formula but it creates blank rows/"false" that I do not want. I can filter out the blank cells using filter function but I have data from 15 different measurements in the same sheet. And want to keep it this way. If I use filter function in one data the other 14 changes. Any suggestion to properly cleanup my data? Really appreciate your help.

    I have attached my excel sheet. The first sheet contains the raw data. In ''Sheet 1'' I tried to use the IF formula.

    Warm regards,
    Amin

  2. #2
    Registered User
    Join Date
    03-01-2022
    Location
    Eire
    MS-Off Ver
    O365
    Posts
    57

    Re: Data clean up

    This is a bit hard to follow. Please cut your sample down to 20 rows and about 2 columns and manually calculate results based on a 10% of maximum exclusion rate. Once we can see the need... adapting it to a 1% exclusion rate will be easy.

  3. #3
    Registered User
    Join Date
    03-01-2022
    Location
    Eire
    MS-Off Ver
    O365
    Posts
    57

    Re: Data clean up

    I figured it out.

    A17:
    =IFERROR(INDEX('Coxiril_CEN_Neg_Batch A1'!A:A,AGGREGATE(15,6,ROW('Coxiril_CEN_Neg_Batch A1'!$B$17:$B$787)/('Coxiril_CEN_Neg_Batch A1'!B$17:B$787>=MAX('Coxiril_CEN_Neg_Batch A1'!B:B)/100),ROWS(A$17:A17))),"")

    B17:
    =IFERROR(INDEX('Coxiril_CEN_Neg_Batch A1'!B:B,AGGREGATE(15,6,ROW('Coxiril_CEN_Neg_Batch A1'!$B$17:$B$787)/('Coxiril_CEN_Neg_Batch A1'!C$17:C$787>=MAX('Coxiril_CEN_Neg_Batch A1'!C:C)/100),ROWS(B$17:B17))),"")

    Select BOTH cells. Copy across and down.

  4. #4
    Registered User
    Join Date
    05-30-2022
    Location
    Mol, Belgium
    MS-Off Ver
    Professional Plus 2016
    Posts
    3

    Re: Data clean up

    Hello Ardross,

    Thank you very much for your comment! I have tried the formula but something is not working. May be I am doing it wrong. Since the excel data was long, I am attaching another file with reduced data. Could you please have a look?

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Data clean up

    Perhaps the following will do what you want:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula is modeled in columns V:AC.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    05-30-2022
    Location
    Mol, Belgium
    MS-Off Ver
    Professional Plus 2016
    Posts
    3

    Re: Data clean up

    Quote Originally Posted by JeteMc View Post
    Perhaps the following will do what you want:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula is modeled in columns V:AC.
    Let us know if you have any questions.
    Thank you very much! The formula works! You are a lifesaver. If you have time, could you please explain how the formula is working/what is the formula doing? I have tried to figure it out but couldn't.

  7. #7
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,422

    Re: Data clean up

    I suggest following along using the Evaluate Formula feature on the Formulas tab.
    The first few steps will show that ROW(A$8:A$18)-ROW(A$7) evaluates to an array of numbers 1:11
    The next few steps will show that $B$8:$B$18>0.01*MAX($B$8:$B$18) evaluates to an array of TRUE's and FALSE's
    Next the two arrays are combined to give an array of valid row numbers.
    ROWS(V$25:V25) evaluates to which row number is chosen (1) and since the 15 is the first argument in the AGGREGATE function that means starting with the smallest.
    INDEX uses that number to determine which of the values in A$8:A$18 to display.
    If there is an error IFERROR will trap it and display a blank cell instead.
    Let us know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. clean up data
    By mmoore5553 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 04-28-2021, 11:37 AM
  2. HELP! Data clean up...
    By cwelsh in forum Excel General
    Replies: 1
    Last Post: 06-10-2020, 04:42 PM
  3. Data clean up
    By diegolrz in forum Access Tables & Databases
    Replies: 2
    Last Post: 01-18-2016, 10:28 AM
  4. [SOLVED] Coding Convention Questions #2 (To clean up or not to clean up)
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 03-31-2014, 08:18 PM
  5. [SOLVED] VBA Code to optimize and clean data- clean out numerical/ or symbol
    By tracylsr in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-05-2012, 04:21 PM
  6. Clean Function to Clean Entire Sheet
    By gema in forum Excel General
    Replies: 7
    Last Post: 11-05-2009, 10:07 AM
  7. [SOLVED] Get out clean XML data
    By Diane in forum Excel General
    Replies: 1
    Last Post: 04-30-2005, 10:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1