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

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)),"")

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

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!

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.

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

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

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

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

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.

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)

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

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