retrieve duplicate values from arrays

[Guest]

My spreadsheet contains the following:
A B C D
1 123 xxxx x1x1 12345
2 101 yyyy y1y1 23456
3 123 zzzz z1z1 34567
4 215 aaaa a1a1 89100

In another spreadsheet, I want to get all data using column A as the
criteria including those with duplicate values and not just the first one. In
effect I want my other worksheet to show:
A B C D
1 123 xxxx x1x1 12345
2 123 zzzz z1z1 34567

 

[Guest]

One way using non array formulas ..

Assume source data in Sheet1's cols A to D,
from row1 down, with key col A

In Sheet2,

Put in A1:
=IF(Sheet1!A1="","",IF(COUNTIF(Sheet1!A:A,Sheet1!A1)>1,ROW(),""))

Put in B1:
=IF(ROW()>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROW())))
Copy B1 across to E1. Select A1:E1, copy down to cover the max expected
extemt of data in Sheet1's key col A. Hide away col A. Cols B to E will
return the required results, all neatly bunched at the top.

 

[Guest]

I tried the formula but it doesn't seem to work. I still get the same row of
data over and over again. What comes out is:

A B C D E
1 4 123 xxxx x1x1 12345
2 5 123 xxxx x1x1 12345

I would really like sheet 2 to look like this:

A B C D
1 101 yyyy y1y1 23456
2 123 xxxx x1x1 12345
3 123 zzzz z1z1 34567
4 215 aaaa a1a1 89100
5 and so on...

Could you please explain further the formulas?

 

[Max]

Not sure what's happening but I think you've somehow got things a
little mixed up here <g>. Kindly re-read your original posting, what
you indicated there as the source data and what you wanted as the
results. The earlier suggestion should have worked and returned
exactly the results that you wanted.

Here's a working sample which illustrates the earlier suggestion:
http://www.savefile.com/files/520223
Retrieve dup values from_array.xls

Take a look at the sample first, then let me know here how it goes ..

 

[Guest]

Sorry for the confusion. What I am aiming for is to organise the data in such
a way that they are sorted according to a key criteria (according to number
ie 101, 123, 215 and so on) and if it is duplicated then it will list all the
data with the same key criteria.

 

[Max]

Ok, don't think it was a case of confusion. Let's stash away the
earlier suggestion and sample which was directed to your original
posting (& your subject line)

What you're asking for now (your 2nd Q) is to
auto-sort Sheet1's source table by the numbers in the key col A

Here's one way to accomplish this

Assume source data in Sheet1's cols A to D,
from row1 down, with key col A (as before)

In Sheet2,

Put in A1:
=IF(Sheet1!A1="","",Sheet1!A1+ROW()/10^10)

Put in B1:
=IF(ROW()>COUNT($A:$A),"",INDEX(Sheet1!A:A,MATCH(SMALL($A:$A,ROW()),$A:
$A,0)))
Copy B1 across to E1. Select A1:E1, copy down to cover the max
expected
extent of data in Sheet1's key col A. Hide away col A. Cols B to E
will
return the required results, all neatly bunched at the top.

Let me know whether the above answers your 2nd Q

 

[Guest]

Here's a working sample to illustrate the suggestion to your 2nd Q:
http://www.savefile.com/files/520753
Auto sort an array.xls

---