Parseing Data

[armstrtb]

I am parsing data to obtain a Low mid and high value for each item (31 through 556).

I have parsed the data to isolate low values to Coloumn F, mid values to coloumn G and high values to coloumng H based on Type (1=low,2=mid,3=high) for each item entry (see below). I now need to obtain one value for each item (see further below for an example).

This is what my spreadsheet looks like

A	B	C	D	E	F	G	H
Item	Type	Range			 Low	Mid	High
31	1	65			65		
31	2	90				90	
31	3	100					100
31	1	65			65		
32	1	65			65		
32	2	90				90	
32	1	65			65		
32	2	90				90	
32	3	300					300
32	3	300					300
32	1	65			65		
33	2	180				180	
33	1	55			55		
33	1	55			55		
33	2	180				180	
33	1	55			55		
33	2	180				180

NOTE: Coloumns F G and H values are constant for each item range
(eg for item 31 all low entries are the same at 65 but it can change from item to item.)

This is what I want to obtain (I just threw this together quickly the values in Low mid and high are not corresponding to the data above)

Item	Low	Mid	High
31	65	75	78
32	55	60	180
33	48	55	60
34	12	24	45
35	168	200	300

I thought about doing a Vlookup for item number and returning the first F G and H values for each item number but is there a way to tell it to only return a number, not blank?
Would a macro work better?

Running Excel 2003

Many Thanks!

 

[MickG]

Try this :-
Results start "J1".

[COLOR="Navy"]Sub[/COLOR] MG25May06
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & rows.Count).End(xlUp))
ReDim Ray(1 To Rng.Count, 1 To 4)
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
[COLOR="Navy"]If[/COLOR] Not .Exists(Dn.value) [COLOR="Navy"]Then[/COLOR]
n = n + 1
Ray(n, 1) = Dn
    [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] True
        [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] = Dn.Offset(, 5) <> "": Ray(n, 2) = Dn.Offset(, 5)
        [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] = Dn.Offset(, 6) <> "": Ray(n, 3) = Dn.Offset(, 6)
        [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] = Dn.Offset(, 7) <> "": Ray(n, 4) = Dn.Offset(, 7)
[COLOR="Navy"]End[/COLOR] Select
.Add Dn.value, n
[COLOR="Navy"]Else[/COLOR]
[COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] True
    [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] = Dn.Offset(, 5) <> "": Ray(.Item(Dn.value), 2) = Dn.Offset(, 5)
    [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] = Dn.Offset(, 6) <> "": Ray(.Item(Dn.value), 3) = Dn.Offset(, 6)
    [COLOR="Navy"]Case[/COLOR] [COLOR="Navy"]Is[/COLOR] = Dn.Offset(, 7) <> "": Ray(.Item(Dn.value), 4) = Dn.Offset(, 7)
[COLOR="Navy"]End[/COLOR] Select
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Range("J1").Resize(.Count, 4) = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[armstrtb]

Thanks, it worked like a charm!

Would you mind explaining explaining your code a bit, I would just like to understand what you did so I can work in getting to a level of competency of my own.


What was the intent of this part of the code?:

ReDim Ray(1 To Rng.Count, 1 To 4)
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare

Thanks again!

 

[MickG]

You really need to "Google" it and also find other post to work through.,
The "Scripting dictionary" basically allows you to place items in a collection.
Firstly, the code places the Unique items from column "A" into column (1) of array "Ray" .
i.e. (.add dn.value, n). Note the "n"
Then the code finds from the select case which column (For that first value) from columns "F,G,H" has a value.
The "n" is in essence the index for that value and known as the "Item".
If the code finds the same Value again , it goes to "Else" in the code and runs the select case for the same value as index "n" again.
It knows what value from column "A" it is because the (Dn.value) is the Item of "n".
Which means the results from columns (F,G,H") for subsequent values of the same unique item are placed in the same row of array(ray)
When All the Unique values are placed in the array (Ray) with the values from columns(F,G,H) then the array (Ray) is returned to the sheet.
You really need to play with examples to get the Hang of it !!!!
Regards Mick

 

[armstrtb]

Thanks I'll start working with collections more and see how it goes.

You're amazing!