I currently have multiple rows per client in a worksheet and would like to
reduce it to ONE row per client. However, I would like to
capture the information from each of the rows when reducing to one row.

For example:
Client No |Account_No|Deposit |
75 1000 700
75 2000 200
62 4500 150
62 5000 500
62 5500 250

I would like it to be:
Client No|Accnt No_a|Accnt No_b|Accnt No_c|Deposit_a|Deposit_b|Deposit_c|
75 1000 2000 700 200
62 4500 5000 5500 150 500
250

Is there a way to do that?

Many thanks,
Dina

Assume your data in A1:C6 with a header in row 1
Criteria in A9 down with a header in row 8

The formulas belowed required ctrl+shift+enter, not just enter

In B9:
=IF(ISERR(SMALL(IF(Client=$A9,ROW(INDIRECT("1:"&RO WS(Account)))),COLUMNS($A:A))),"",INDEX(Account,SM ALL(IF(Client=$A9,ROW(INDIRECT("1:"&ROWS(Account)) )),COLUMNS($A:A))))

copy across to D9

In E9:
=IF(ISERR(SMALL(IF(Client=$A9,ROW(INDIRECT("1:"&RO WS(Account)))),COLUMNS($A:A))),"",INDEX(Deposit,SM ALL(IF(Client=$A9,ROW(INDIRECT("1:"&ROWS(Account)) )),COLUMNS($A:A))))

coppy across to G9
select B9:G9 and copy down


"Dina" wrote:

I currently have multiple rows per client in a worksheet and would like to
reduce it to ONE row per client. However, I would like to
capture the information from each of the rows when reducing to one row.

For example:
Client No |Account_No|Deposit |
75 1000 700
75 2000 200
62 4500 150
62 5000 500
62 5500 250

I would like it to be:
Client No|Accnt No_a|Accnt No_b|Accnt No_c|Deposit_a|Deposit_b|Deposit_c|
75 1000 2000 700 200
62 4500 5000 5500 150 500
250

Is there a way to do that?

Many thanks,
Dina

Thank you very much for the solution. I have a follow-up question:

I am dealing with a larget number of rows, number of clients and number of
variables (columns). Is there a way in excel to generate the variable names
in the result table automatically:

Ex:
Initially
Client no | Account no | Deposit no | Withdrawal no | closing date| etc etc
etc

Result
Client no | Account no 1 | Account no 2| |Account no 3| |Account no 4|
|Deposit no 1| Deposit no 2| Deposit no 3| |Deposit no 4| |Withdrawal no 1|
etc etc

Is there way in excel to generate the above column headings automatically?

Thannk you very much






"Teethless mama" wrote:

Assume your data in A1:C6 with a header in row 1
Criteria in A9 down with a header in row 8

The formulas belowed required ctrl+shift+enter, not just enter

In B9:
=IF(ISERR(SMALL(IF(Client=$A9,ROW(INDIRECT("1:"&RO WS(Account)))),COLUMNS($A:A))),"",INDEX(Account,SM ALL(IF(Client=$A9,ROW(INDIRECT("1:"&ROWS(Account)) )),COLUMNS($A:A))))

copy across to D9

In E9:
=IF(ISERR(SMALL(IF(Client=$A9,ROW(INDIRECT("1:"&RO WS(Account)))),COLUMNS($A:A))),"",INDEX(Deposit,SM ALL(IF(Client=$A9,ROW(INDIRECT("1:"&ROWS(Account)) )),COLUMNS($A:A))))

coppy across to G9
select B9:G9 and copy down


"Dina" wrote:

I currently have multiple rows per client in a worksheet and would like to
reduce it to ONE row per client. However, I would like to
capture the information from each of the rows when reducing to one row.

For example:
Client No |Account_No|Deposit |
75 1000 700
75 2000 200
62 4500 150
62 5000 500
62 5500 250

I would like it to be:
Client No|Accnt No_a|Accnt No_b|Accnt No_c|Deposit_a|Deposit_b|Deposit_c|
75 1000 2000 700 200
62 4500 5000 5500 150 500
250

Is there a way to do that?

Many thanks,
Dina