Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display unique data only
I have a spreadsheet, and on the first sheet i have various client data. To
avoid having to re-enter some of the data on sheet two, I have entered formulas to pull it through from sheet 1. It is pulling through, ref number, name, and a start date. It is likely that some people could appear more than once, but I only want them to show once on sheet 2. I have tried to use advanced filter, but it is looking at the formula rather than the result, so it is not filtering out the repeated data. Is there anything i can do to get round this, I have to get it finished for next week and i am starting to panic! Please help! Thanks Becks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display unique data only
On the second sheet
A1: = Sheet1!A1 A2: =IF(ISERROR(MATCH(0,COUNTIF(A$1:A1,Sheet1!$A$1:$A$ 20&""),0)),"", INDEX(IF(ISBLANK(Sheet1!$A$1:$A$20),"",Sheet1!$A$1 :$A$20),MATCH(0,COUNTIF(A$ 1:A1,Sheet1!$A$1:$A$20&""),0))) which is an array formula, it should be comnmitted with Ctrl-Shift-Enter, not just Enter. Copy A2 down to some point beyond the last item (to cater for extras). -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Becks" wrote in message ... I have a spreadsheet, and on the first sheet i have various client data. To avoid having to re-enter some of the data on sheet two, I have entered formulas to pull it through from sheet 1. It is pulling through, ref number, name, and a start date. It is likely that some people could appear more than once, but I only want them to show once on sheet 2. I have tried to use advanced filter, but it is looking at the formula rather than the result, so it is not filtering out the repeated data. Is there anything i can do to get round this, I have to get it finished for next week and i am starting to panic! Please help! Thanks Becks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display unique data only
Thanks Bob, this works fine for the ref number and name, but people do have
the same start date, any ideas? "Bob Phillips" wrote: On the second sheet A1: = Sheet1!A1 A2: =IF(ISERROR(MATCH(0,COUNTIF(A$1:A1,Sheet1!$A$1:$A$ 20&""),0)),"", INDEX(IF(ISBLANK(Sheet1!$A$1:$A$20),"",Sheet1!$A$1 :$A$20),MATCH(0,COUNTIF(A$ 1:A1,Sheet1!$A$1:$A$20&""),0))) which is an array formula, it should be comnmitted with Ctrl-Shift-Enter, not just Enter. Copy A2 down to some point beyond the last item (to cater for extras). -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Becks" wrote in message ... I have a spreadsheet, and on the first sheet i have various client data. To avoid having to re-enter some of the data on sheet two, I have entered formulas to pull it through from sheet 1. It is pulling through, ref number, name, and a start date. It is likely that some people could appear more than once, but I only want them to show once on sheet 2. I have tried to use advanced filter, but it is looking at the formula rather than the result, so it is not filtering out the repeated data. Is there anything i can do to get round this, I have to get it finished for next week and i am starting to panic! Please help! Thanks Becks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display unique data only
Becks,
Does the same name have the same ref? If so no problem, if not do we uniquify on name, or name and ref? Then do you want latest date or earliest date if we still get dups? -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Becks" wrote in message ... Thanks Bob, this works fine for the ref number and name, but people do have the same start date, any ideas? "Bob Phillips" wrote: On the second sheet A1: = Sheet1!A1 A2: =IF(ISERROR(MATCH(0,COUNTIF(A$1:A1,Sheet1!$A$1:$A$ 20&""),0)),"", INDEX(IF(ISBLANK(Sheet1!$A$1:$A$20),"",Sheet1!$A$1 :$A$20),MATCH(0,COUNTIF(A$ 1:A1,Sheet1!$A$1:$A$20&""),0))) which is an array formula, it should be comnmitted with Ctrl-Shift-Enter, not just Enter. Copy A2 down to some point beyond the last item (to cater for extras). -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Becks" wrote in message ... I have a spreadsheet, and on the first sheet i have various client data. To avoid having to re-enter some of the data on sheet two, I have entered formulas to pull it through from sheet 1. It is pulling through, ref number, name, and a start date. It is likely that some people could appear more than once, but I only want them to show once on sheet 2. I have tried to use advanced filter, but it is looking at the formula rather than the result, so it is not filtering out the repeated data. Is there anything i can do to get round this, I have to get it finished for next week and i am starting to panic! Please help! Thanks Becks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display unique data only
Hi Bob - The data is set up as follows:
ColA ColB ColC Ref No Name Start Date 123 Bloggs 01/04/05 123 Bloggs 18/12/05 321 Jones 01/04/05 The ref number for each person is always the same. So going off the example, i just want to show rows 1 & 3, as row 2 is a duplicate. I entered the formula in Col A, and copied it down, I changed the Col ref in the formula and did the same in Col B and it worked but when i tried it in the date column it didn't work. I'ts probably just me doing something wrong, hopefully this explains it better. Thanks for your time. Becks "Bob Phillips" wrote: Becks, Does the same name have the same ref? If so no problem, if not do we uniquify on name, or name and ref? Then do you want latest date or earliest date if we still get dups? -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Becks" wrote in message ... Thanks Bob, this works fine for the ref number and name, but people do have the same start date, any ideas? "Bob Phillips" wrote: On the second sheet A1: = Sheet1!A1 A2: =IF(ISERROR(MATCH(0,COUNTIF(A$1:A1,Sheet1!$A$1:$A$ 20&""),0)),"", INDEX(IF(ISBLANK(Sheet1!$A$1:$A$20),"",Sheet1!$A$1 :$A$20),MATCH(0,COUNTIF(A$ 1:A1,Sheet1!$A$1:$A$20&""),0))) which is an array formula, it should be comnmitted with Ctrl-Shift-Enter, not just Enter. Copy A2 down to some point beyond the last item (to cater for extras). -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Becks" wrote in message ... I have a spreadsheet, and on the first sheet i have various client data. To avoid having to re-enter some of the data on sheet two, I have entered formulas to pull it through from sheet 1. It is pulling through, ref number, name, and a start date. It is likely that some people could appear more than once, but I only want them to show once on sheet 2. I have tried to use advanced filter, but it is looking at the formula rather than the result, so it is not filtering out the repeated data. Is there anything i can do to get round this, I have to get it finished for next week and i am starting to panic! Please help! Thanks Becks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display unique data only
Okay, try it this way
A1: = Sheet1!A1 A2: =IF(ISERROR(MATCH(0,COUNTIF(A$1:A1,Sheet1!A$1:A$20 &""),0)),"", INDEX(IF(ISBLANK(Sheet1!A$1:A$20),"",Sheet1!A$1:A$ 20),MATCH(0,COUNTIF(A$1:A1 ,Sheet1!A$1:A$20&""),0))) Copy A1:A2 acros to C1:C2 Then copy A2:C2 down as far as required. As before A2 is an array formula, so do that before copying -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Becks" wrote in message ... Hi Bob - The data is set up as follows: ColA ColB ColC Ref No Name Start Date 123 Bloggs 01/04/05 123 Bloggs 18/12/05 321 Jones 01/04/05 The ref number for each person is always the same. So going off the example, i just want to show rows 1 & 3, as row 2 is a duplicate. I entered the formula in Col A, and copied it down, I changed the Col ref in the formula and did the same in Col B and it worked but when i tried it in the date column it didn't work. I'ts probably just me doing something wrong, hopefully this explains it better. Thanks for your time. Becks "Bob Phillips" wrote: Becks, Does the same name have the same ref? If so no problem, if not do we uniquify on name, or name and ref? Then do you want latest date or earliest date if we still get dups? -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Becks" wrote in message ... Thanks Bob, this works fine for the ref number and name, but people do have the same start date, any ideas? "Bob Phillips" wrote: On the second sheet A1: = Sheet1!A1 A2: =IF(ISERROR(MATCH(0,COUNTIF(A$1:A1,Sheet1!$A$1:$A$ 20&""),0)),"", INDEX(IF(ISBLANK(Sheet1!$A$1:$A$20),"",Sheet1!$A$1 :$A$20),MATCH(0,COUNTIF(A$ 1:A1,Sheet1!$A$1:$A$20&""),0))) which is an array formula, it should be comnmitted with Ctrl-Shift-Enter, not just Enter. Copy A2 down to some point beyond the last item (to cater for extras). -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Becks" wrote in message ... I have a spreadsheet, and on the first sheet i have various client data. To avoid having to re-enter some of the data on sheet two, I have entered formulas to pull it through from sheet 1. It is pulling through, ref number, name, and a start date. It is likely that some people could appear more than once, but I only want them to show once on sheet 2. I have tried to use advanced filter, but it is looking at the formula rather than the result, so it is not filtering out the repeated data. Is there anything i can do to get round this, I have to get it finished for next week and i am starting to panic! Please help! Thanks Becks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display unique data only
It works fine in Columns A & B, but with the dates in Column C, its showing
the same date in every cell I copy it down to? "Bob Phillips" wrote: Okay, try it this way A1: = Sheet1!A1 A2: =IF(ISERROR(MATCH(0,COUNTIF(A$1:A1,Sheet1!A$1:A$20 &""),0)),"", INDEX(IF(ISBLANK(Sheet1!A$1:A$20),"",Sheet1!A$1:A$ 20),MATCH(0,COUNTIF(A$1:A1 ,Sheet1!A$1:A$20&""),0))) Copy A1:A2 acros to C1:C2 Then copy A2:C2 down as far as required. As before A2 is an array formula, so do that before copying -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Becks" wrote in message ... Hi Bob - The data is set up as follows: ColA ColB ColC Ref No Name Start Date 123 Bloggs 01/04/05 123 Bloggs 18/12/05 321 Jones 01/04/05 The ref number for each person is always the same. So going off the example, i just want to show rows 1 & 3, as row 2 is a duplicate. I entered the formula in Col A, and copied it down, I changed the Col ref in the formula and did the same in Col B and it worked but when i tried it in the date column it didn't work. I'ts probably just me doing something wrong, hopefully this explains it better. Thanks for your time. Becks "Bob Phillips" wrote: Becks, Does the same name have the same ref? If so no problem, if not do we uniquify on name, or name and ref? Then do you want latest date or earliest date if we still get dups? -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Becks" wrote in message ... Thanks Bob, this works fine for the ref number and name, but people do have the same start date, any ideas? "Bob Phillips" wrote: On the second sheet A1: = Sheet1!A1 A2: =IF(ISERROR(MATCH(0,COUNTIF(A$1:A1,Sheet1!$A$1:$A$ 20&""),0)),"", INDEX(IF(ISBLANK(Sheet1!$A$1:$A$20),"",Sheet1!$A$1 :$A$20),MATCH(0,COUNTIF(A$ 1:A1,Sheet1!$A$1:$A$20&""),0))) which is an array formula, it should be comnmitted with Ctrl-Shift-Enter, not just Enter. Copy A2 down to some point beyond the last item (to cater for extras). -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Becks" wrote in message ... I have a spreadsheet, and on the first sheet i have various client data. To avoid having to re-enter some of the data on sheet two, I have entered formulas to pull it through from sheet 1. It is pulling through, ref number, name, and a start date. It is likely that some people could appear more than once, but I only want them to show once on sheet 2. I have tried to use advanced filter, but it is looking at the formula rather than the result, so it is not filtering out the repeated data. Is there anything i can do to get round this, I have to get it finished for next week and i am starting to panic! Please help! Thanks Becks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display unique data only
On Fri, 20 Jan 2006 09:17:05 -0800, "Becks"
wrote: Hi Bob - The data is set up as follows: ColA ColB ColC Ref No Name Start Date 123 Bloggs 01/04/05 123 Bloggs 18/12/05 321 Jones 01/04/05 The ref number for each person is always the same. So going off the example, i just want to show rows 1 & 3, as row 2 is a duplicate. I entered the formula in Col A, and copied it down, I changed the Col ref in the formula and did the same in Col B and it worked but when i tried it in the date column it didn't work. I'ts probably just me doing something wrong, hopefully this explains it better. Thanks for your time. Becks Here's another way: Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/forums Assumption: Your data is entered on Sheet1! with labels in row 1 and data in A2:C100 Sheet2!A2: =INDEX(UNIQUEVALUES(Sheet1!$A$2:$A$100,1),ROWS($1: 1)) Sheet2!B2: =IF($A2="","",VLOOKUP($A2,Sheet1!$A$2:$C$100,2,FAL SE)) Sheet2!C2: =IF($A2="","",VLOOKUP($A2,Sheet1!$A$2:$C$100,3,FAL SE)) Select A2:C2 and copy/drag down as far as needed. --ron |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display unique data only
Absolutely fantastic!!!! Thanx very much! Just what i was looking for.
Cheers Becks "Ron Rosenfeld" wrote: On Fri, 20 Jan 2006 09:17:05 -0800, "Becks" wrote: Hi Bob - The data is set up as follows: ColA ColB ColC Ref No Name Start Date 123 Bloggs 01/04/05 123 Bloggs 18/12/05 321 Jones 01/04/05 The ref number for each person is always the same. So going off the example, i just want to show rows 1 & 3, as row 2 is a duplicate. I entered the formula in Col A, and copied it down, I changed the Col ref in the formula and did the same in Col B and it worked but when i tried it in the date column it didn't work. I'ts probably just me doing something wrong, hopefully this explains it better. Thanks for your time. Becks Here's another way: Download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/forums Assumption: Your data is entered on Sheet1! with labels in row 1 and data in A2:C100 Sheet2!A2: =INDEX(UNIQUEVALUES(Sheet1!$A$2:$A$100,1),ROWS($1: 1)) Sheet2!B2: =IF($A2="","",VLOOKUP($A2,Sheet1!$A$2:$C$100,2,FAL SE)) Sheet2!C2: =IF($A2="","",VLOOKUP($A2,Sheet1!$A$2:$C$100,3,FAL SE)) Select A2:C2 and copy/drag down as far as needed. --ron |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Display unique data only
On Wed, 25 Jan 2006 02:50:02 -0800, "Becks"
wrote: Absolutely fantastic!!!! Thanx very much! Just what i was looking for. Cheers Becks Glad to help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I display the actual data in the data field of a pivot chart? | Excel Discussion (Misc queries) | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions | |||
Macro to search for and display data in another worksheet | Excel Worksheet Functions | |||
Sort pages? | Excel Discussion (Misc queries) |