Multidimensional array

**gwithey** · 02-02-2009, 05:14 AM

I am stuck at the moment with this problem.

I need to store 2 columns of data into an array Columns 8 and 9.

I then need to find all matching data in column 8 ie (7X6605,7X6605)(there are 1000's of these combinations)

With these matching strings i need to sort them by column9 which is time(30/12/1899 09:07:01) so that the erliest time is first.

I Cant get my head round this problem does anyone know how it can be done.

Bellow My attempt:

Please Login or Register  to view this content.

Thanx George

**Andy Pope** · 02-02-2009, 05:27 AM

Can you post small example of the data

**gwithey** · 02-02-2009, 05:49 AM

I have worked out the solution a bit better with labled comments.

This is where i have got to now

Please Login or Register  to view this content.

**gwithey** · 02-02-2009, 05:50 AM

Data example column 8 cerial num

7X6605
7X6605
7X6621
7X6628
7X6628
7X6628
1Q1111
1Q1111
7X6406
7X6406
7X6406
7X6407
7X6416
7X6450
7X6450
7X6453
7X6453
7X6474
7X6474
7X6476
7X6477
7X6479
7X6479
7X6479
7X6481
7X6482

Column 9 Time

30/12/1899 09:07:01
30/12/1899 09:10:29
30/12/1899 09:19:46
30/12/1899 09:15:49
30/12/1899 09:23:37
30/12/1899 09:14:08
30/12/1899 12:15:05
30/12/1899 12:16:15
30/12/1899 11:28:31
30/12/1899 11:29:37
30/12/1899 12:11:32
30/12/1899 11:03:05
30/12/1899 11:17:31
30/12/1899 10:53:15
30/12/1899 10:55:42
30/12/1899 11:51:06
30/12/1899 11:53:07
30/12/1899 11:21:48
30/12/1899 11:24:41
30/12/1899 11:37:02
30/12/1899 11:45:46
30/12/1899 11:08:23
30/12/1899 11:10:18
30/12/1899 11:06:35
30/12/1899 11:13:47
30/12/1899 11:57:45

**Andy Pope** · 02-02-2009, 06:12 AM

I'm lost.

Do you just want the contents of columns 8 and 9 in an array sorted by columns 8 and then 9?

**gwithey** · 02-02-2009, 06:37 AM

The arry is created already sorted by serial number as i sort the sheet then add the data to the array.

i just need to somehow extract matching cerial numbers from the array into another sheet, then i can sort them by time on the created sheet.

**gwithey** · 02-02-2009, 06:38 AM

This is how i was trying to extract matching cerial numbers and enter them into a new sheet, it just says type miss match

**gwithey** · 02-02-2009, 06:39 AM

Please Login or Register  to view this content.

**Andy Pope** · 02-02-2009, 06:50 AM

Which line errors?

What information is written out if the serial numbers match? First/last/all dates?

Why not post a workbook of the sample data in a before and after state?

**gwithey** · 02-02-2009, 07:10 AM

Here is an example.

Its so hard to explain the macro is simpler to understand

George

**gwithey** · 02-02-2009, 07:19 AM

If serial numbers match say
MsgBox ArrCombine(1)(2)
MsgBox ArrCombine(2)(2)
MsgBox ArrCombine(3)(2)
Shown above if the array index is 2 on all the column arrays they will all be in the same column

**Andy Pope** · 02-02-2009, 07:22 AM

It's going to be pretty hard for anybody to help if you can not explain your problem

Let's try understanding why you are using the array as the loop index variable?

Please Login or Register  to view this content.

Should you not be using Rw2 or some other variable?

Can you not use some formula and autofilter?

**gwithey** · 02-02-2009, 07:40 AM

lol yes i see your point there.

I am inexprerienced never used arrays before

I was just trying to come up with a way of getting all the same cerial numbers and adding them to a sheet

**gwithey** · 02-02-2009, 07:46 AM

This is closer to the mark but not quite right as it pints the title heading 9999 times apposed to the data in the column

Please Login or Register  to view this content.

George

**Andy Pope** · 02-02-2009, 07:54 AM

You are not using the correct variable within the loop.

Please Login or Register  to view this content.

I doubt this does exactly what you want either.

**gwithey** · 02-02-2009, 08:10 AM

That didnt work well there was a loop in another loop so it did 9999 for 9999 loops.

Please Login or Register  to view this content.

This nearly works just doesnt extract the same serial numbers

**Andy Pope** · 02-02-2009, 09:57 AM

It's still not clear to me what the processed records should look like.

Did your attached file contain the final output? If so which sheet.

**gwithey** · 02-02-2009, 10:23 AM

I attatched the erlier file which showed the out put all i need is to work out the 1 or 0 for passed or fail and put this informatin onto the ftp sheet which the macro generates.

When the macro has finished the only thing left should be the sheet with a list of 1's and 0's which can then be used to calculate the first time pass rate

Sorry for my explination skills, sometimes hard to do in text

George

**Andy Pope** · 02-02-2009, 10:33 AM

Try this formula in K2 of Sheet3 and copy down.

=IF(COUNTIF($H$2:$H$10,H2)>1,IF(ISERROR(SEARCH("FAILED",F2)),0,1),"")

Does that give you the 0 and 1's you expect?

**gwithey** · 02-02-2009, 10:55 AM

That does work it out. I have done this in the code bellow and have been told it is the wrong way to do it which is why i have to create this long complicated macro instead of one easy formula.

They told me to sort sheet 3 by serial num.
Make array to store it in along with test passed and time
Then sort these by time to find the first test in each group of serial numbers that match.
This will give me the actual first time pass rate.

Hope thats a clearer outline of the problem

Seems an odd way of doing it but im working for someone else

If you run this code it will show all the work i have done as that example doesnt have it. (Not Code for current prob for rest of workbook)

**gwithey** · 02-02-2009, 10:56 AM

Please Login or Register  to view this content.

**gwithey** · 02-02-2009, 10:57 AM

Follows last code

Please Login or Register  to view this content.

**Andy Pope** · 02-02-2009, 11:20 AM

This uses a slight variation on the formula to ignore Aborted tests.

Please Login or Register  to view this content.

**gwithey** · 02-02-2009, 11:38 AM

Thats nearly it.
The sheet should only contain one serial number
Sheet deleted to save memory
sort by time and then put the passed result in a results sheet

**gwithey** · 02-02-2009, 11:40 AM

is that all the tests that passed if so all i would need to do is count those and divde by total tests for fo FTP rate

**Andy Pope** · 02-02-2009, 11:46 AM

Sorry but this just does not make sense.

The sheet should only contain one serial number
Sheet deleted to save memory
sort by time and then put the passed result in a results sheet

If the sheet only contains 1 serial number what is the point of sorting?
And with only 1 number the memory overhead is hardly going to be large.

**gwithey** · 02-02-2009, 11:52 AM

Dont worry about that i just didnt understand what had happened or what the sheet was showing

What did the macro do

And what results are left are they the sorted FTP because this will have solved the problem

George

**Andy Pope** · 02-02-2009, 11:57 AM

The macro uses formula in column T of sheet3 to flag occurances of multiple serial numbers that have a PASSED status.

It then goes through column T and copies serial number and date to FTP sheet.

**gwithey** · 02-02-2009, 12:02 PM

Thats great has it flagged and exported the single serial numbers as well bacause these count as first time pass as well

George

**Andy Pope** · 02-02-2009, 12:05 PM

Based on the example you provided the only 2 records it creates are

7X6605 30/12/1899 09:10:29
7X6628 30/12/1899 09:23:37

These are not reported as they are single occurances
7X6621
7X6406

This is not reported as it had not Passed.
1Q1111

**gwithey** · 02-02-2009, 12:11 PM

Thats great.

I have got the result i wanted just confused both of us as i was told to do it in a long winded way.

Thanx alot for the help as you have dedicated a few hours to heling out

George

**Andy Pope** · 02-02-2009, 12:15 PM

No problem glad we got it sorted.
Obviously you will need to check it on your real data set.

Multidimensional array

LinkBack

Thread Tools

Rate This Thread

Display

Multidimensional array

re: Multidimensional array

re: Multidimensional array

re: Multidimensional array

re: Multidimensional array

re: Multidimensional array

re: Multidimensional array

re: Multidimensional array

re: Multidimensional array

re: Multidimensional array

re: Multidimensional array

re: Multidimensional array

re: Multidimensional array

re: Multidimensional array

re: Multidimensional array

re: Multidimensional array

re: Multidimensional array

re: Multidimensional array

Re: Multidimensional array

Re: Multidimensional array

Re: Multidimensional array

Re: Multidimensional array

Re: Multidimensional array

Re: Multidimensional array

Re: Multidimensional array

Re: Multidimensional array

Re: Multidimensional array

Re: Multidimensional array

Re: Multidimensional array

Re: Multidimensional array

Re: Multidimensional array

Re: Multidimensional array

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions