Posts by Ophiuchus

Re: Complex macro to reduce consecutive values in ranges to minimun expression

Hello shknbk2,

Many many thanks for your help and time used to get this code that works great. I've tested and works much faster
than your first version when I try ten thousands.

Very appreciated your help again.

Best regards

Re: Complex macro to reduce consecutive values in ranges to minimun expression

Hello again shknbk2,

Good to know your creativity exploded :angrypc:. It works good enough for my current needs

My original problem actually is that I know the numbers (for first example 3024 and 3028) and I need to generate the output
that you get with your code, but I was not able to do it. I thought to split in 2 steps the problem and I was able to generate only
list of numbers that your program uses as input, putting "ffh" to known numbers to differentiate from the others. The 2nd step
was generate the output you get, but I stucked in that part.

Maybe if you want it could be possible to obtain the output that produces your current code but only knowing the numbers
that should be marked with "ffh". The known numbers always begin with the same digit and for my first example, the input
only would be 3024 and 3028.

Many thanks again.

Regards

Re: Complex macro to reduce consecutive values in ranges to minimun expression

Hello shknk2,

Thanks so much for your help and time. It seems to work when number of values are 1000(X000 to X999).
I'll try to understand your logic to see if I can extend to handle when values are 10000.

Thanks again.

Best regards

Hello to all,

Maybe someone could help me with a macro to do the following complex arrangement.
In attached sample file I have in range A1:A1000 consecutive values from 3000 to 3999. Some numbers have next to them the string "ffh".
In this example it happens like this,

- From 3000 to 3009 --> There are no numbers associated with "ffh"
- From 3010 to 3019 --> There are no numbers associated with "ffh"
- From 3020 to 3029 --> There are 2 numbers associated with "ffh" (3024 adn 3028)
- From 3030 to 3999 --> There are no numbers associated with "ffh"

What I would like is to represent in the minimin expression the ranges that don't have "ffh" within it, so, for this
case, instead to have numbers from 3000 to 3009 I'd like to have only 300 (since 300 is common to all numbers from 3000 to 3009),
and instead to have numbers from 3010 to 3019 I'd like to have only 301.

Another example that something that happens in range A1:A1000 is:

From 3300 to 3399 there are no numbers with "ffh", then I'd like to have only 33. (33 is common for numbers in range 3300 to 3399)

Then the final array will be reduced as shown in column B (B1:B28)

I hope make sense

Thanks in advance for any help.

Re: How to manipulate scripting dictionary of more than one dimension

Hello snb,

Your code works for me too. But in my code I have a kind of a dictionary of second level since instead of

.item("x")=Array(...)

I have

.item("x")("y")=Array(...)

And using your code adding the "2nd level" dictionary, I cannot store the values into an array using twice ".item.item", something like:

sn3 = Application.Index(.item.item.items, 0, 0) 'Run-time error 450: "Wrong number of arguments or invalid property assignment

Sub M_snb()
    With CreateObject("scripting.dictionary")
        .item("aa1") = 1
        .item("bb1") = 1
        
        Set .item("aa1") = CreateObject("scripting.dictionary")
        .item("aa1")("cc1") = Array(11, 12, 13)
        
        Set .item("bb1") = CreateObject("scripting.dictionary")
        .item("bb1")("dd1") = Array(21, 22, 23)
        
        sn1 = Application.Index(.item("aa1").items, 0, 0) 'OK. stores values of .item("aa1")
        sn2 = Application.Index(.item("bb1").items, 0, 0) 'OK. stores values of .item("bb1")
        
        sn3 = Application.Index(.item.item.items, 0, 0) 'NOK. Is not working with this sentence store all values of parent Dictionary in one array
    End With
End Sub

Re: How to manipulate scripting dictionary of more than one dimension

Hi snb,

I get run-time error 450: "Wrong number of arguments or invalid property assignment" using the sentence below.

Arr3 = application.index(dic1.items,0,0)

Thanks for any help

Re: How to manipulate scripting dictionary of more than one dimension

Hello snb,

Thanks for the example and link. I've seen many good examples. One question.

Based on the code in my first post ans with the code below I'm able to store in array the keys and the items, but I'm not able to store the values
associated with the items. In the code shared by danerida is done looping.

It is possible to assign the values (3,2,4 and 5,7,8) to an array with a similar command like below for keys and item without looping?

Arr1 = Dic1.keys()  'Copy keys into Arr1 ("a" and "b")
Arr2 = Dic1.items() 'Copy the items into Arr2 ("x","y" and "z")
Arr3=? ' (How to get Arr3(0)=Array(3,2,4) and Arr3(1)=Array(5,7,8))

Thanks again

Re: How to manipulate scripting dictionary of more than one dimension

Hello danerida,

Thanks for the help.

I've changed a little bit your code and it seems to work. Then it looks like for a dictionary like this there is no way to assign the values
with a single command as when assign the keys.

For Each item In Dic1
        out(i, ii) = item
        ii = ii + 1
        For Each it In Dic1.items()(i).items
            out(i, ii) = it
            ii = ii + 1
        Next it
        i = i + 1
        ii = 0
    Next item

Thanks again.

Hello to all,

Please some help here.

I have and dictionary of 2 dimensions. The code is like below:

Sub Dic()


Set Dic1 = CreateObject("scripting.dictionary")
With Dic1
    .Item("a") = 1
    Set .Item("a") = CreateObject("scripting.dictionary")
    .Item("a")("x") = 3
    .Item("a")("y") = 2
    .Item("a")("z") = 4
    
    .Item("b") = 2
    Set .Item("b") = CreateObject("scripting.dictionary")
    .Item("b")("x") = 5
    .Item("b")("y") = 7
    .Item("b")("z") = 8
End With
End Sub

I'd like to manipulate the content of the Dic1 in order to get an array like shown below.
How can I do this?

Out(0, 0) = "a": Out(0, 1) = 3: Out(0, 2) = 2: Out(0, 3) = 4
Out(1, 0) = "b": Out(1, 1) = 5: Out(1, 2) = 7: Out(1, 3) = 8

I've tested with code below without success.

Out = Array(Dic1.keys(), Dic1.items())

Thanks in advance for any thelp

Re: Obtain range associated with data validation list

Excellent snb, just exactly what I want.

Thanks so much!

Re: Obtain range associated with data validation list

Hi snb,

I'm attaching a sample file.

Thanks you!

Hi to all in forum,

I've been trying to do this without success, please some help on this.

I have in A1 a dropdown list with validation criteria = List. In source is associated with a named range named "Validations".

When I see Name Manager, the range named "Validations" is associated with "Sheet2!$B$2:$C$10"

How can I get the real range related with the dropdown list that should be "Sheet2!$B$2:$C$10"?

When I try with the code below I get only "=Validations" and not the real range expected that is "Sheet2!$B$2:$C$10" .

Sub RangeFromDropdown() 
Dim ValRng As String  


ValRng = Sheets("sheet1").Range("A1").Validation.Formula1 


End Sub

Any help would be appreciated.

Hello to all,

I have the Range B:F for "Values MJ" and columns G:K with "Values RT". In column A there is a letter for each row
but some letters could appear more than once and I want to have only unique letters in column A and merged the
Range B:F for "Values MJ" and columns G:K with "Values RT" in the same row for those repeated letters.

Original data:
[TABLE="class: grid, width: 1040"]

[TD="width: 80"][/TD]
[TD="width: 80"]A[/TD]
[TD="width: 80"]B[/TD]
[TD="width: 80"]C[/TD]
[TD="width: 80"]D[/TD]
[TD="width: 80"]E[/TD]
[TD="width: 80"]F[/TD]
[TD="width: 80"]G[/TD]
[TD="width: 80"]H[/TD]
[TD="width: 80"]I[/TD]
[TD="width: 80"]J[/TD]
[TD="width: 80"]K[/TD]
[TD="width: 80"]L[/TD]

[TD="align: right"]1[/TD]

[TD="colspan: 5"]Values MJ[/TD]
[TD="colspan: 5"]Values RT[/TD]

Values JHK

[TD="align: right"]2[/TD]
[TD="width: 80"]Letter[/TD]
[TD="width: 80"]G
[/TD]
[TD="width: 80"]H[/TD]
[TD="width: 80"]T[/TD]
[TD="width: 80"]W[/TD]
[TD="width: 80"]S[/TD]
[TD="width: 80"]G
[/TD]
[TD="width: 80"]H[/TD]
[TD="width: 80"]T[/TD]
[TD="width: 80"]W[/TD]
[TD="width: 80"]S[/TD]
[TD="width: 80"]U[/TD]

[TD="align: right"]3[/TD]

B

4

2

0

0

6

0

[TD="align: right"]4[/TD]

C

1

1

2

1

5

1

[TD="align: right"]5[/TD]

A

2

0

4

2

8

2

[TD="align: right"]6[/TD]

R

7

22

8

4

41

1

[TD="align: right"]7[/TD]

C

0

3

10

5

18

[TD="align: right"]8[/TD]

T

1

4

12

6

23

[TD="align: right"]9[/TD]

A

7

2

3

1

11

[/TABLE]

If a letter only have values in range B:F print "Missing" in range G:K for the same row.
If a letter only have values in range G:K print "Missing" in range B:F for the same row.

I'd like to delete the row of the letter if the repeated letter has values in B:F, since letters for ranges B:F
always appear after the letters that have values in range G:K and since I'd like the output in same sheet.

Output desired:
[TABLE="class: grid, width: 1040"]

[TD="width: 80"][/TD]
[TD="width: 80"]A[/TD]
[TD="width: 80"]B[/TD]
[TD="width: 80"]C[/TD]
[TD="width: 80"]D[/TD]
[TD="width: 80"]E[/TD]
[TD="width: 80"]F[/TD]
[TD="width: 80"]G[/TD]
[TD="width: 80"]H[/TD]
[TD="width: 80"]I[/TD]
[TD="width: 80"]J[/TD]
[TD="width: 80"]K[/TD]
[TD="width: 80"]L[/TD]

[TD="align: right"]1[/TD]
[TD="width: 80"]Letter[/TD]
[TD="colspan: 5"]Values MJ[/TD]
[TD="colspan: 5"]Values RT[/TD]

Values JHK

[TD="align: right"]2[/TD]
[TD="width: 80"]G
[/TD]
[TD="width: 80"]H[/TD]
[TD="width: 80"]T[/TD]
[TD="width: 80"]W[/TD]
[TD="width: 80"]S[/TD]
[TD="width: 80"]G
[/TD]
[TD="width: 80"]H[/TD]
[TD="width: 80"]T[/TD]
[TD="width: 80"]W[/TD]
[TD="width: 80"]S[/TD]
[TD="width: 80"]U[/TD]

[TD="align: right"]3[/TD]

B

Missing

Missing

Missing

Missing

Missing

4

2

0

0

6

0

[TD="align: right"]4[/TD]

C

0

3

10

5

18

1

1

2

1

5

1

[TD="align: right"]5[/TD]

A

7

2

3

1

11

2

0

4

2

8

2

[TD="align: right"]6[/TD]

R

Missing

Missing

Missing

Missing

Missing

7

22

8

4

41

1

[TD="align: right"]7[/TD]

T

1

4

12

6

23

Missing

Missing

Missing

Missing

Missing

[/TABLE]

I hope make sense.

Thanks in advance.

Re: Complex printing of values of unique strings between 2 arrays

Thank you so much for the example and explanation MickG, very kind of you.

Thanks again for the help.

Re: Complex printing of values of unique strings between 2 arrays

Hello MickG,

Many thanks for your help. This time it seems to work very nice!

Do you know a link where can I see how to use the concepts applied in your code in regards to items of 2 dimensions ".Items(()())"?

I beginning seeing the scripting dictionary and I've only seen the use of" items" with one dimension.

Thanks again

Re: Complex printing of values of unique strings between 2 arrays

Hello MickG,

Thank you for your help and time. It works fine with original arrays, but if you change A1 by B1, A2 by B2 and viceversa, the output is not correct.

I mean if you use these arrays, is not work.

ArryA1 = Array("D", "X", "F", "J")
    ArryA2 = Array(Array(6, 0, 2, 1), Array(1, 1, 6, 1), Array(7, 9, 1, 0), Array(0, 3, 2, 2))
    
    ArryB1 = Array("A", "D", "F")
    ArryB2 = Array(Array(0, 1, 4, 5), Array(2, 0, 1, 1), Array(2, 3, 11, 3))

Maybe you can help me to fix it.

Thanks again

Hello to all,

I have the following arrays.

A1=Array("A","D","F")
A2 = Array(Array(0, 1, 4, 5), Array(2, 0, 1, 1), Array(2, 3, 11, 3))

B1=Array("D","X","F","J")
B2 = Array(Array(6, 0, 2, 1), Array(1, 1, 6, 1), Array(7, 9, 1, 0),Array(0, 3, 2, 2))

I would like to print in column A all the unique/common values between Array A1 and Array B1. The common values are A,D,F,X,J.
And I'd like to print in Columns D:G the A2(i) values and print in columns I:L the values B2(i) in the corresponding row of values
of arrays A1 and B1. If some string only appears in array A1 (i.e. "A"), this means that string "A" have values associated in array A2, but not in B2, then print zeros from I:L and if the string only appears in array B1 (i.e. "X" and "J"), this means that strings "X" and "J" have values associated in array B2, but don't have valus in A2, then print zeros from D:G for the same row of X and J.

The output desired is in file attached, I hope make sense.

Many thanks for any help.

Regards

Re: Printing in specific order content of scripting dictionary

Hello Krishnakumar and jindon,

Many thanks for such great solutions. Both work perfect for my case.

You both introduced me in a new topic of VBA as scripting dictionary that I didn't know about it.

Thanks for help others guys!

Best regards

Re: Printing in specific order content of scripting dictionary

Hello Krishnakumar,

Exactly! it works so fine, thank you.

2 questions. In your code how to force that the order of W,X,Y,Z be exactly that, but without sort alphabetically?
Since instead of W the string could be Price, instead of X could be Quantity and so on, and I'd like to have them in that order, not alphabetically.

And the other one is, how to sort the fruits alphabetically?

Thanks again for all the help.

Regards

Re: Printing in specific order content of scripting dictionary

Hello Krishnakumar,

Thanks for your help and share a new approach. It works very well!

Since I'll need to print the fruits in column A and the values for W, X, Y, Z in column D, how to print
separated (lets say in 2 steps) the fruits and the columns of w,x,y,z without erase the values in columns B-C?

Thanks again.

Regards