Array?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ag - 06 Mar 2008 20:45 GMT

I Have a list like the one below
I would like to writre a formula that places the id numbers along side each
other in columns the the vehicle, price and insurer is the same. Any help
would be appreciated.

Vehicles Description    Price    Insurer    ID
Holden - Other    $228.00    CIC-Alianz    409
Holden - Other    $228.00    CIC-Alianz    416
Holden - Other    $228.00    CIC-Alianz    829
Holden - Other    $228.00    CIC-Alianz    836
Holden - Other    $228.00    QBE    202
Holden - Other    $228.00    QBE    209
Holden - Other    $228.00    QBE    412
Holden - Other    $228.00    QBE    419

Reply to this Message

Bob Phillips - 06 Mar 2008 21:11 GMT

Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = LastRow To 3 Step -1

If .Cells(i, "A").Value = .Cells(i - 1, "A").Value Then

.Cells(i, "C").Resize(1, 100).Copy .Cells(i - 1, "D")
.Rows(i).Delete
End If
Next i

End With

End Sub

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I Have a list like the one below
> I would like to writre a formula that places the id numbers along side
[quoted text clipped - 11 lines]
> Holden - Other $228.00 QBE 412
> Holden - Other $228.00 QBE 419

Reply to this Message

Max - 07 Mar 2008 03:50 GMT

Bob,
Tried running your sub on the OP's data set (assumed in cols A to D, data
from row2 down),
but couldn't get it to return expected results.

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Bob Phillips - 07 Mar 2008 10:35 GMT

It did in my test Max. How many columns did you setup, I used 3, and it
collapsed it all to one line.

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

> Bob,
> Tried running your sub on the OP's data set (assumed in cols A to D, data
> from row2 down),
> but couldn't get it to return expected results.

Reply to this Message

Max - 07 Mar 2008 13:10 GMT

> .. How many columns did you setup

I used the OP's 4 cols

Thought the OP wanted the result to look something like this:
Holden $228.00 CIC-Alianz ..409 416 829 836
Holden $228.00 QBE ........202 209 412 419

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> It did in my test Max. How many columns did you setup, I used 3, and it
> collapsed it all to one line.

Reply to this Message

Bob Phillips - 07 Mar 2008 19:49 GMT

What do you mean 4? I read it as Vehicles Description, Price and Insurance
Id <G>

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>> .. How many columns did you setup
> I used the OP's 4 cols
[quoted text clipped - 4 lines]
>> It did in my test Max. How many columns did you setup, I used 3, and it
>> collapsed it all to one line.

Reply to this Message

Harlan Grove - 08 Mar 2008 20:58 GMT

"Max" <demecha...@yahoo.com> wrote...

>Tried running your sub on the OP's data set (assumed in cols A to D,
>data from row2 down),
>but couldn't get it to return expected results.

...

Don't you think it would have been useful to mention what results it
did produce?

Anyway, the OP asked for formulas, so why not?

If the original data, not including column headings, were in an 8 row
by 3 column range named Data (so treating insurer and id number as one
field), put the result headers in E1:H1, and let the first result row
begin in E2.

E2:
=INDEX(Data,1,1)

F2:
=INDEX(Data,1,2)

G2:
=LEFT(INDEX(Data,1,3),FIND(CHAR(127),SUBSTITUTE(INDEX(Data,1,3),
" ",CHAR(127),LEN(INDEX(Data,1,3))-LEN(SUBSTITUTE(INDEX(Data,1,3),
" ",""))))-1)

H2:
=IF(SUMPRODUCT((INDEX(Data,0,1)=$E2)*(INDEX(Data,0,2)=$F2)
*ISNUMBER(FIND($G2,INDEX(Data,0,3))))>=COLUMNS($H2:H2),
SUBSTITUTE(INDEX(Data,COLUMNS($H2:H2),3),$G2&" ",""),"")

Fill H2 right as far as reasonable.

E3:
=INDEX(Data,COUNTIF($G$2:$IV2,"?*"),1)

F3:
=INDEX(Data,COUNTIF($G$2:$IV2,"?*"),2)

G3:
=LEFT(INDEX(Data,COUNTIF($G$2:$IV2,"?*"),3),FIND(CHAR(127),
SUBSTITUTE(INDEX(Data,COUNTIF($G$2:$IV2,"?*"),3)," ",CHAR(127),
LEN(INDEX(Data,COUNTIF($G$2:$IV2,"?*"),3))
-LEN(SUBSTITUTE(INDEX(Data,COUNTIF($G$2:$IV2,"?*"),3)," ",""))))-1)

H3:
=IF(SUMPRODUCT((INDEX(Data,0,1)=$E3)*(INDEX(Data,0,2)=$F3)
*ISNUMBER(FIND($G3,INDEX(Data,0,3))))>=COLUMNS($H3:H3),
SUBSTITUTE(INDEX(Data,COUNTIF($H$2:$IV2,"?*")+COLUMNS($H3:H3),3),
$G3&" ",""),"")

Fill H3 right as far as reasonable, say to Z3. Then fill E3:Z3 down as
far as needed.

OTOH, if Data spanned 4 columns, with insurer and id in separate
fields, and id as actual numbers, with the first result cell in A12,

A12:
=INDEX(Data,1,1)

B12:
=INDEX(Data,1,2)

C12:
=INDEX(Data,1,3)

D12:
=INDEX(Data,1,4)

E12:
=IF(SUMPRODUCT((INDEX(Data,0,1)=$A12)*(INDEX(Data,0,2)=$B12)
*(INDEX(Data,0,3)=$C12))>=COLUMNS($D12:E12),INDEX(Data,
COLUMNS($D12:E12),4),"")

Fill E12 right as far as reasonable.

A13:
=INDEX(Data,COUNT($D$12:$IV12)+1,1)

B13:
=INDEX(Data,COUNT($D$12:$IV12)+1,2)

C13:
=INDEX(Data,COUNT($D$12:$IV12)+1,3)

D13:
=INDEX(Data,COUNT($D$12:$IV12)+1,4)

E13:
=IF(SUMPRODUCT((INDEX(Data,0,1)=$A13)*(INDEX(Data,0,2)=$B13)
*(INDEX(Data,0,3)=$C13))>=COLUMNS($D13:E13),INDEX(Data,
COUNT($D$12:$IV12)+COLUMNS($D13:E13),4),"")

Fill E13 right as far as reasonable, say to Z13. Then fill A13:Z13
down as far as needed.

Both sets of formulas assume the original data is sorted.

Reply to this Message