+ Reply to Thread
Results 1 to 15 of 15

Auto Populate repetitive data on master to a single row on another sheet excel 2013

  1. #1
    Registered User
    Join Date
    06-03-2013
    Location
    US
    MS-Off Ver
    Excel 2016
    Posts
    41

    Auto Populate repetitive data on master to a single row on another sheet excel 2013

    Hi all,

    I want working to consolidate certain information on my master list to a single row. for example. my master list contains contact information that is enter over and over again throughout the year for different information listed by dates.

    i am making a new sheet that will auto populate just the contact information, but only ONCE. Vlookup will locate my contact over and over again and so will =sheet!1A2 making it s massive repeative list i don't want in sheet two. Is there a formula or if statement that will look for certain data source on master list (sheet 1) and if its already listed, it will not include in on sheet two?

    thanks for your help

  2. #2
    Forum Contributor
    Join Date
    06-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: Auto Populate repetitive data on master to a single row on another sheet excel 2013

    I do something similar. Here's the layout of mine, so you can understand the formula.

    Small collection of stuff in A7:A31. Down in A167 I have this, using Ctrl+Shift+Enter as it's an array formula:
    =INDEX($A$7:$A$31,MATCH(0,COUNTIF($A$7:$A$31,"<"&$A$7:$A$31),0))

    Directly under that, in A168, I have this formula. Also enter with Ctrl+Shift+Enter, and then copy and paste down as necessary:
    =IF(COUNTIF($A$7:$A$31,">"&A167),INDEX($A$7:$A$31,MATCH(COUNTIF($A$7:$A$31,"<="&A167),COUNTIF($A$7:$A$31,"<"&$A$7:$A$31),0)),"")

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,915

    Re: Auto Populate repetitive data on master to a single row on another sheet excel 2013

    A variation to that which I learned on here, using a regular formula is...

    =IFERROR(INDEX($A$7:$A$31,MATCH(0,INDEX(COUNTIF($D$1:D1,$AB$7:$AB$31),0,0),0)),"")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    06-03-2013
    Location
    US
    MS-Off Ver
    Excel 2016
    Posts
    41

    Re: Auto Populate repetitive data on master to a single row on another sheet excel 2013

    I am a bit lost. is your formula on the sheet you are condensing or its on the master? i need its to copy and delimit C2:C12325 , J2:J12325 on sheet one. i have uses IFERROR before but it very specific so i want to make sure i enter it correctly.

    thanks for your help with this

  5. #5
    Forum Contributor
    Join Date
    06-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: Auto Populate repetitive data on master to a single row on another sheet excel 2013

    Quote Originally Posted by airedale360 View Post
    I am a bit lost. is your formula on the sheet you are condensing or its on the master? i need its to copy and delimit C2:C12325 , J2:J12325 on sheet one. i have uses IFERROR before but it very specific so i want to make sure i enter it correctly.

    thanks for your help with this
    Do you mean copy only unique values from C2:C12325 from sheet one onto J2:J12325 on another sheet? I don't understand your statement.


    Quote Originally Posted by FDibbins View Post
    A variation to that which I learned on here, using a regular formula is...

    =IFERROR(INDEX($A$7:$A$31,MATCH(0,INDEX(COUNTIF($D$1:D1,$AB$7:$AB$31),0,0),0)),"")
    Good to know. I'll have to play with this one a bit. The one I initially posted sorts it alphabetically, which I kinda like. But the use of a non-array would have advantages. Appreciate having an alternative!

  6. #6
    Registered User
    Join Date
    06-03-2013
    Location
    US
    MS-Off Ver
    Excel 2016
    Posts
    41

    Re: Auto Populate repetitive data on master to a single row on another sheet excel 2013

    i need to copy all the data in columns C through J on my master sheet. to the new sheet. the problem is sometimes the same information is listed multiple times throughout the year on the master. i only want to record this data once on my new sheet. but its a lot of data the formula will have to pull. i could have it set that is column C data checked for repeated information and it will not copy over if found more then once. and if its not it will include all the data on column C to Column J from the master to the new sheet.

  7. #7
    Forum Contributor
    Join Date
    06-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: Auto Populate repetitive data on master to a single row on another sheet excel 2013

    Do you have a sample data set, scrubbed of identifying information?

  8. #8
    Registered User
    Join Date
    06-03-2013
    Location
    US
    MS-Off Ver
    Excel 2016
    Posts
    41

    Re: Auto Populate repetitive data on master to a single row on another sheet excel 2013

    so i built a mock up in google docs. as this is a new year i don't have much data, but my master list will continuously grow. sheet 2 need to grab columns B-E only and only include them once. i hope this helps:

    https://docs.google.com/spreadsheets...it?usp=sharing

  9. #9
    Forum Contributor
    Join Date
    06-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: Auto Populate repetitive data on master to a single row on another sheet excel 2013

    Sheets does the arrays a bit different than Excel. If you're sticking with Excel, what's in the sheet now should work.

  10. #10
    Registered User
    Join Date
    06-03-2013
    Location
    US
    MS-Off Ver
    Excel 2016
    Posts
    41

    Re: Auto Populate repetitive data on master to a single row on another sheet excel 2013

    sorry your last post lost me.. what are you trying to say?

  11. #11
    Forum Contributor
    Join Date
    06-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: Auto Populate repetitive data on master to a single row on another sheet excel 2013

    Sheets in google docs does things differently than Excel does.

    I added the formulas to your google docs sheet. Export back to Excel and try it out.

  12. #12
    Forum Contributor
    Join Date
    06-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: Auto Populate repetitive data on master to a single row on another sheet excel 2013

    In Excel, it would be this on Sheet2:

    Cell A2 (array):
    =INDEX(Master1!$B$2:$B$12,MATCH(0,COUNTIF(Master1!$B$2:$B$12,"<"&Master1!$B$2:$B$12),0))

    Cell A3 (array):
    =IF(COUNTIF(Master1!$B$2:$B$12,">"&Master1!$B$2:$B$12),INDEX(Master1!$B$2:$B$12,MATCH(COUNTIF(Master1!$B$2:$B$12,"<="&A2),COUNTIF(Master1!$B$2:$B$122,"<"&Master1!$B$2:$B$12),0)),"")

    Copy A3 on down.

    B2, copy down B:
    =INDEX(Master1!$A$2:$G$12,MATCH($A2,Master1!$B$2:$B$12,0),3)

    C2:
    =INDEX(Master1!$A$2:$G$12,MATCH($A2,Master1!$B$2:$B$12,0),4)

    D2:
    =INDEX(Master1!$A$2:$G$12,MATCH($A2,Master1!$B$2:$B$12,0),5)

  13. #13
    Registered User
    Join Date
    06-03-2013
    Location
    US
    MS-Off Ver
    Excel 2016
    Posts
    41

    Re: Auto Populate repetitive data on master to a single row on another sheet excel 2013

    when i remove { i get N/A if i keep it. it shows the formula not the data

  14. #14
    Forum Contributor
    Join Date
    06-21-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    191

    Re: Auto Populate repetitive data on master to a single row on another sheet excel 2013

    The { indicates it is an array formula. Do not type the {. Instead, insert the formula without the { and press Control+Shift+Enter

  15. #15
    Registered User
    Join Date
    06-03-2013
    Location
    US
    MS-Off Ver
    Excel 2016
    Posts
    41

    Re: Auto Populate repetitive data on master to a single row on another sheet excel 2013

    woohoo formula one worked - formula two for A3 works but its copies the same name from above not the next name from the master. i dont need the same name twice
    Last edited by airedale360; 03-20-2015 at 07:08 PM.

+ 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. Auto-Populate Sheets Based on Master Sheet
    By agulbino in forum Excel General
    Replies: 7
    Last Post: 02-10-2015, 08:25 PM
  2. Auto Populate Master Sheet from Multiple Other Workbooks
    By HangMan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-28-2014, 10:22 AM
  3. Want to auto populate an excel sheet from a master sheet of data
    By M>E> in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-07-2014, 03:34 PM
  4. Replies: 2
    Last Post: 02-10-2012, 02:00 PM
  5. Auto Populate Master Sheet with multiple sheets data
    By haley in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-26-2009, 04:29 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