Pick info from Multiple Columns & rows from Unique value

Hi.

Your profile says Excel 2007, but you've attached a 2003 sheet.

Can you clarify whether this solution needs to be compatible with 2003?

Regards

I'd do it this way:

First go to Name Manager and define:

Name: ID_NIC
Refers to: =$A$2:$A$20

Name: Contacts
Refers to: =$B$2:$H$20

Name: Arry1
Refers to: =1+(INT((ROW(INDEX($A:$A,1):INDEX($A:$A,ROWS(Contacts)*COLUMNS(Contacts)))-1)/COLUMNS(Contacts)))

Name: Arry2
Refers to: =1+(MOD((ROW(INDEX($A:$A,1):INDEX($A:$A,ROWS(Contacts)*COLUMNS(Contacts)))-1),COLUMNS(Contacts)))

The formula in cell A25 is then:

=IFERROR(SMALL(IF(FREQUENCY(ID_NIC,ID_NIC),ID_NIC),ROWS($1:1)),"")

Copy down until you start to get blanks for the results.

And the array formula** in B25 is:

=IF(A25="","",IFERROR(SMALL(IF(FREQUENCY(IF(ID_NIC=$A25,IF(Contacts<>"",0+(Contacts&0))),0+(Contacts&0)),INDEX(Contacts,N(IF(1,Arry1)),N(IF(1,Arry2)))),COLUMNS($A:A)),""))

Copy across and down until you start to get blanks for the results.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

You're very welcome!

If you need any help understanding any parts of this construction then just reply in the thread and I'll do my best to explain.

Cheers

Sure. But while we're on the subject, can I ask if it's also possible for there to be any blanks in between non-blank entries in that column (which would currently be an issue for the set-up I gave you)?

Regards

Thanks.

And does your results table in actuality fall directly below your data? I only ask as this makes the creation of a dynamic range which determines the last-used cell in that table a touch problematic (though not impossible).

(A results table under an expanding source table in any case seems a little unwise, if you don't mind my saying so. )

Regards

Ok, so if you make one further addition to Name Manager:

Name: LRow
Refers to: =MATCH(88^88,contactos_cliente.rdl!$A:$A)

which assumes that your values in column A of that sheet will all be numerical, then your definitions for ID_NIC and Contacts are now:

Name: ID_NIC
Refers to: =contactos_cliente.rdl!$A$2:INDEX(contactos_cliente.rdl!$A:$A,LRow)

Name: Contacts
Refers to: =contactos_cliente.rdl!$B$2:INDEX(contactos_cliente.rdl!$H:$H,LRow)

and both will now respond dynamically to additions/removals to that source data (based on their being a numerical entry in column A, of course).

Note that deleting row 2 within this sheet will cause these definitions to fail.

Regards

Sure! Glad to help!

Cheers