PDA

View Full Version : Creating sub-blocks within a large data-block



Stargazer
07-16-2010, 05:13 AM
Hi,

I've spent a long time lurking around the knowledge base picking up great little tidbits of code to use and it's helping me to learn more and more about VBA evetry time. So a big thanks to all the contributors!

But it's finally time for me to break my forum virginity here as I've run into a problem and require assistance.

I have a large block of data that contains customer contract information from our retail group. Three important fields we need to work with are IMEI number, SIM number and MPN (Mobile Phone Number).

Because of the way our point of sale system works, when a branch captures a sale, the transaction is divided up to either 2 or more lines, depending on how many accessories the customer buys at the same time.

The stupid POS system though, is putting the IMEI on one line and the SIM and MPN on one of the other lines. We have a ridiculous If statement that sort of corrects this but its not ideal.

The real issue though, is that we're concerned that possibly a branch may have stuffed up the sale and not generated one or more of these important bits of data.

What we need is a way for Excel to step through the data block, acknowledge that lines 2, 3, 4 & 5 are for customer Smith so it then checks that sub-block for the relevant criteria. It then needs to acknowledge that lines 6 & 7 are for customer Jones and check fro relevant criteria, and so on.

I'm trying to think of all the times I've used the 'introw' and 'lLastrow' commands in VBA, but am struggling to figure out how to make excel do what I'm thinking.

Perhaps there's an easier way. Perhaps there's a really tragically difficult way. But if there is a way, I'd really like to hear about it.

I hope I've explained this clearly enough. If you need any additional perspective then let me know and I'll see if I can provide.

Many thanks in advance,

Rob.

PS. I'm running Office 2007 in WinXP SP3. Don;t know how relevant that it but figured it couldn't hurt to mention it.

GTO
07-16-2010, 05:37 AM
Greetings Stargazer:hi:

First and foremost, welcome to vbaexpress!:)

I am quite sure that you'll be happy you joined, and soon wonder, "Why didn't I before?" Leastwise, that was certainly my thought soon after joining.

As to your question, could you post a wb with some fake data that accurately portrays the layout and what we are wanting to do?

Again, welcome here!

Mark

Stargazer
07-16-2010, 06:05 AM
Hi,

Thanks for the welcome and the quick reply. I'm pretty confident you;re right about the "Why didn't I do this before..." thing. For several months now, the guy I work with on all the excel projects and I have been tripping over ourselves while we try to find solutions when we probably could have saved ourselves heaps of stress by just asking someone...

ho-hum.

Anyway, I've faked some data in a vastly condensed version of our output file. You'll see that customer data comes out all bunched up so that multi line transactions are all int he same area, but not all lines contain the all important IMEI, SIM and MPN entries. For the record, the only bit there I have faked/masked is the actual numbers. The blanks and the 0's... thats how we see it too.

Hope this makes sense.

Many thanks,

Rob.

GTO
07-16-2010, 04:45 PM
Hi Rob,

Not meant as a solution, just to see if this is where headed. Probably not the best way, but to see if all three vals exist...


Option Explicit

Sub exa()
Dim aryData As Variant, aryRecords As Variant, i As Long, ldataUnqRow As Long

'// Using last cell in Col A w/data to set bottom, plunk data into an array //
With Sheet1 ' or ThisWorkbook.Worksheets("Sheet1")
aryData = _
.Range(.Range("A3"), .Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, "L")).Value
End With

With CreateObject("Scripting.Dictionary")

'// build a collection of unique URI's //
For i = 1 To UBound(aryData, 1)
.Item(aryData(i, 1)) = aryData(i, 1)
Next

'// Size and fill on column of our output array, resize to fit other stuff //
ReDim aryRecords(1 To .Count, 1 To 1)
aryRecords = Application.Transpose(.Items)
ReDim Preserve aryRecords(1 To .Count, 1 To 13)

For i = 1 To UBound(aryData, 1)

'// See which row in aryRecords we want to be putting any found vals into //
ldataUnqRow = Application.Match(aryData(i, 1), .Items, 0)

'// if IMEI has a legit val (meaning just not zero or empty at this point), //
'// add to correct element of output array //
If Not aryData(i, 8) = 0 And Not aryData(i, 8) = vbNullString Then
aryRecords(ldataUnqRow, 8) = aryData(i, 8)
End If
'// ...SIM...
If Not aryData(i, 9) = 0 And Not aryData(i, 9) = vbNullString Then
aryRecords(ldataUnqRow, 9) = aryData(i, 9)
End If
'// ...MPN...
If Not aryData(i, 10) = 0 And Not aryData(i, 10) = vbNullString Then
aryRecords(ldataUnqRow, 10) = aryData(i, 10)
End If
Next
End With

'// Run down first col of output array and mark if all three vals exist //
For i = 1 To UBound(aryRecords, 1)
aryRecords(i, 13) = CBool(Len(aryRecords(i, 8))) _
And CBool(Len(aryRecords(i, 9))) _
And CBool(Len(aryRecords(i, 10)))
Next

'// just demo results //
With Worksheets.Add(, ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)) _
.Range("A1").Resize(UBound(aryRecords, 1), UBound(aryRecords, 2))

.Value = aryRecords
.EntireColumn.AutoFit
End With
End Sub

See if that's anything like what you are trying to determine.

I should have mentioned, but if more samples are needed, .xls format is viewable by more (including yours truly if at home...).

Mark

Stargazer
07-19-2010, 01:02 AM
Hi Rob,

See if that's anything like what you are trying to determine.

I should have mentioned, but if more samples are needed, .xls format is viewable by more (including yours truly if at home...).

I'll hopefully try this out a bit later today and get back to you. Many thanks for putting it together. I have another project on the go at the moment that has a deadline so have to see to that first, but will get onto this as soon as it's done. I didn't want to let this just sit in case you thought I was a selfish ungrateful git.

I'll also try to remember the .xls thing. I guess I've been bowled over by the new file formats that Office12 offers and forget that other people might still be running other versions.

Thanks again... Watch this space.

Rob.

Stargazer
07-20-2010, 06:19 AM
Hi,

I've just tested the code you posted and the results are interesting. I now have a list of the URI's and a True/False tag for each one.

This is very close to what I was looking for. Not quite how I imagined it would go about doing it but this will work very nicely all the same.

Considering the number of product types we have for these mobile broadband sales (and lots of the time, people not paying attention when keying details) not every URI will contain a value in all three of the field I'm interested in.

Some kind of a breakdown would be the next step. One that says a sale with no details is a concern, one with a missing IMEI is forehead wrinkler, one with missing sim is a minor inconvenience... That kind of thing.

I'll spend a few minutes reading throguh what you've already done to try and get to grips with it. Annoyingly, I'm having to really redline my grey-matter to make sense of VBA... I'm not a natural programmer. The internet, especially the OZGrid forums and the VBAX knowledge base have been my friends over the last several weeks.

Thanks again,

Rob.