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

1. ## 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?

2. ## 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. ## 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)),"")

4. ## 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. ## Re: Auto Populate repetitive data on master to a single row on another sheet excel 2013

Originally Posted by airedale360
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.

Originally Posted by FDibbins
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. ## 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. ## 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. ## 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:

9. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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

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