Array?

  • Thread starter Thread starter ag
  • Start date Start date
A

ag

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
 
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

--
---
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.

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

--
---
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

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
 
What do you mean 4? I read it as Vehicles Description, Price and Insurance
Id <G>

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Max said:
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.
Click to expand...
....

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.
 
Back
Top