FILL dictionary

I have thi sheet and 2 column

AAAAA 123456
AAAAA 128456
AAAAA 123457
BBBBB 123456
BBBBB 128863
BBBBB 129226
CCCCC 234556

i need to loop all cells into sheet and fill a dictionary in this mode:

AAAAA not exists add, AAAAA and related value 123456

AAAAA just exists, add 128456 to the already exists combination

ecc


to the and of cell loop the dictionary filled

Quote:

---

AAAAA just exists, add 128456 to the already exists combination

---

as a dictionary key can only have one item, the example uses a tab delimited string

Code:

---

For Each c In Range("a:a")
    If IsEmpty(c) Then Exit For  ' stop on empty cell
    If Not d.Exists(Trim(c.Value)) Then d(Trim(c.Value)) = c.Offset(, 1).Text Else d(Trim(c.Value)) = d(Trim(c.Value)) & vbTab & c.Offset(, 1).Value
Next

---

where d is a scripting dictionary object
this is tested using your sample data, trim may not be required, but solved some issue i was having

Quote:

---

Originally Posted by westconn1

as a dictionary key can only have one item, the example uses a tab delimited string

Code:

---

For Each c In Range("a:a")
    If IsEmpty(c) Then Exit For  ' stop on empty cell
    If Not d.Exists(Trim(c.Value)) Then d(Trim(c.Value)) = c.Offset(, 1).Text Else d(Trim(c.Value)) = d(Trim(c.Value)) & vbTab & c.Offset(, 1).Value
Next

---

where d is a scripting dictionary object
this is tested using your sample data, trim may not be required, but solved some issue i was having

---

NICE!

But how to looping and retrive data from the dictionary collection,

in effect i need to loop for all item the related key...


i think with a 2 for two for next cicles...but how to?

there is an example from microsoft here http://support.microsoft.com/kb/187234

Quote:

---

in effect i need to loop for all item the related key...

---

as i specified above each key can only have a single item, so using the previous example, try like

Code:

---

items = split(d("AAAAA"), vbtab)
for each i in items
  debug.print i
next

---

Quote:

---

Originally Posted by westconn1

there is an example from microsoft here http://support.microsoft.com/kb/187234

as i specified above each key can only have a single item, so using the previous example, try like

Code:

---

items = split(d("AAAAA"), vbtab)
for each i in items
  debug.print i
next

---

---

Have you tested ....?

many error in definition of variable...:(

Quote:

---

many error in definition of variable

---

you can fix, i also created a dictionary object to work with