VBA: Match StringA to Cell Contents and Write IntegerB to a cell Based on the match

Thank you for looking at my question,

I am reading strings from a csv file (sample attached) and writing to a worksheet to create a table 1 row per item.

in the target worksheet (by the time the code gets to this point) I have written column headings (from Column D) :

Row1: Year
Row2: Period (period=month number)

The extent of the columns used is based upon user input so will vary but for this example

eg

           Column Column Column Column Column Column Column Column Column Column
               D             E             F             G             H             I             J             K             L             M
Row1: 2014       2014      2014       2014       2014      2014      2014      2014      2015      2015
Row2:    5              6             7              8             9            10         11           12          1              2

I capture the year value from the source file and combine with the period value:  Year &"_"& period
I want to read the value of column row 1 and combine with the value of column row 2 in the format Year&"_"&Period compare the values and if a match write the Actual Issue value (source file column 2) to Row 3


See my code below:

                If IsNumeric(RTrim(LTrim(Left(strLine, 11)))) = True Then
                    intSourcePeriod = getPeriod(strLine)

                    strComparator_Source = intSourceYear & "_" & intSourcePeriod
                    
                    Do Until strMatch = "Bingo!"

''XXXXXXXXXXXXX This is where I have the issue XXXXXXXXXX

                        strComparator_Array = .Cells(intWriteCol, 1).Value & "_" & .Cells(intWriteCol, 2).Value

''XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

                        If StrComp(strComparator_Source, strComparator_Array) = 0 Then
                            .Cells(lngRowNumber, intWriteCol) = Format(intActualIssue, "#,##0.0000")
                            intSummedIssue = intSummedIssue + intActualIssue
                            intWriteCol = intWriteCol + 1
                            strMatch = "Bingo!"
                        Else
                            .Cells(lngRowNumber, intWriteCol) = 0
                            intWriteCol = intWriteCol + 1
                        End If
                    Loop
                End If

Open in new window



Any help you can offer will be much appreciated
PeriodIssueData-Out.txt
Gary CroxfordOperations Support AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

aikimarkCommented:
I have no idea what you are trying to do, based on either your problem description or the file you posted.
0
Martin LissOlder than dirtCommented:
It would help greatly if you could attach your workbook or a sample workbook that contains all your code and explain the steps needed to get to the point where you have written the column headings.
0
[ fanpages ]IT Services ConsultantCommented:
Do you require output like this?

---
           Column Column Column Column Column Column Column Column Column Column
               D             E             F             G             H             I             J             K             L             M
Row1: 2014       2014      2014       2014       2014      2014      2014      2014      2015      2015
Row2:    5              6             7              8             9            10         11           12          1              2
Row3:                                              2.0000    8.0000   6.0000   5.0000  3.0000   2.0000   6.0000
---

If so, or in any case, are you opening the Comma-Separated Values file using an Open statement, & reading each row sequentially, or do you open the entire file as an MS-Excel worksheet?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

aikimarkCommented:
@fanpages

The posted file is not CSV formatted.  It looks like the output of a report.
0
[ fanpages ]IT Services ConsultantCommented:
Hi aikimark,

Thanks.  Sorry, yes, I am guessing it is the same file as mentioned in this question (from yesterday):

[ http://www.experts-exchange.com/questions/28705256/VBA-Is-it-possible-to-define-a-range-of-string-variables-dynamically-based-upon-the-number-of-elements-in-array.html ]

It sounds like a very onerous task, & I am sure many of us reading could help Crxfrd, but the approach to wording questions has, so far, been confusing.


Crxfrd: Perhaps we can assist you to a greater degree if you summarise (in bullet points) what you need to achieve, & any interested party can then advise their choice of method of reaching individual goals of the entire project.
0
[ fanpages ]IT Services ConsultantCommented:
PS. aikimark: You deserve recognition of the effort here:

"ID: 40924773"
0
aikimarkCommented:
@Crxfrd

Which of the detail values, on the month lines, do you need?
0
aikimarkCommented:
You deserve recognition
Thanks, fanpages.
0
aikimarkCommented:
Change the path in the string literal an run this code
Sub Q_28705565()
    Dim oRE As Object
    Dim oMatches As Object
    Dim oM As Object
    Dim oMatches_Month As Object
    Dim oM_Month As Object
    Dim strData As String
    Dim intFN As Integer
    Dim rngTgt As Range
    
    intFN = FreeFile
    Open "C:\Users\AikiMark\Downloads\PeriodIssueData-Out.txt" For Input As #intFN
    strData = Input(LOF(intFN), intFN)
    Close

    Application.ScreenUpdating = False
    ActiveSheet.Range(Cells(1, 1), Cells(1, 15)).Value = Array("Warehouse", "Item", "Year", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
    Set rngTgt = ActiveSheet.Cells(2, 1)

    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "Warehouse[^:]*:\s*(\S[^\r]*),\r\nItem[^:]*:\s*(\S[^\r]*),\r\nYear[^:]*:\s*(\d{4})((?:.|\n)*?)(?:\|\,)"

    If oRE.test(strData) Then
        Set oMatches = oRE.Execute(strData)
        oRE.Pattern = "\n\s*(\d+)\s+\|\s+(\d[^\s]*)\s"
        
        For Each oM In oMatches
            With oM
                Set oMatches_Month = oRE.Execute(.submatches(3))
                rngTgt.Value = .submatches(0)
                rngTgt.Offset(0, 1).Value = .submatches(1)
                rngTgt.Offset(0, 2).Value = .submatches(2)
                For Each oM_Month In oMatches_Month
                    rngTgt.Offset(0, 2 + oM_Month.submatches(0)).Value = oM_Month.submatches(1)
                Next
            End With
            Set rngTgt = rngTgt.Offset(1)
        Next
    End If
    Application.ScreenUpdating = True
End Sub

Open in new window

1
Gary CroxfordOperations Support AnalystAuthor Commented:
Fanpages,

Do you require output like this?

---
           Column Column Column Column Column Column Column Column Column Column
               D             E             F             G             H             I             J             K             L             M
Row1: 2014       2014      2014       2014       2014      2014      2014      2014      2015      2015
Row2:    5              6             7              8             9            10         11           12          1              2
Row3:                                              2.0000    8.0000   6.0000   5.0000  3.0000   2.0000   6.0000


that's precisely the result I'm looking for with,  maybem zeros in columnd D, E and F

Aikimark, it is the output from a report as a csv file


I want to read the item number, description, warehouse and values  from the 'Actual Issue' column of the report into one row per Item Number.

I can extract the Item Number, Description, Warehouse, Year, Period and Actual Issue from the report. Everything else is covered I just want to know how to write the Actual Issue value to the correct column.

As I failed to explain, my thinking is

read a line from the report that has a period value at the front
Make a string from the Year value and period value eg "2014_8"
Compare that string with another string made by looping through columns D to end making a string to compare against from cell contents D1_D2 to end
If there's a match write the actual issue value, no matcgh write a zero

I hope that's clearer
0
aikimarkCommented:
My code transforms it as follows:
Warehouse	Item                                                                       	Year	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
12345 XXXXX	1917009989                                  Opt 2000 S100 wired inner ass	2014								2	8	6	5	3
12345 XXXXX	1917009989                                  Opt 2000 S100 wired inner ass	2015	2	6	1									

Open in new window

0
Gary CroxfordOperations Support AnalystAuthor Commented:
Aikimark, thank you but I do need the data on one line and to retain the period numbers.

The rest of my code works fine, does what I want it to do. What I am having trouble with is how do I get the value in cell D1 and the value in cell D2 so that I can combine them into a string that I can compare against anothere string for a match and if there is no match loop throughD1D2, E1E2, F1F2 to the last occupied cell in row 1 until a match is found

The looping I can do, no problem, it's extracting the value that is in the cell - I tried the line below but it is wrong andf I need somebody to translate it into working code

''XXXXXXXXXXXXX This is where I have the issue XXXXXXXXXX

                        strComparator = .Cells(4, 1).Value & "_" & .Cells(4, 2).Value

''XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

As before, any guidance you can give will be much appreciated
0
aikimarkCommented:
Even though the file has an extension of .csv, it is a text file.  When you import the file, Excel is not parsing it the way you need.  I think that is the root cause of your coding problem.
0
aikimarkCommented:
This version of the routine:
Sub Q_28705565()
    Dim oRE As Object
    Dim oMatches As Object
    Dim oM As Object
    Dim oMatches_Month As Object
    Dim oM_Month As Object
    Dim strData As String
    Dim intFN As Integer
    Dim rngTgt As Range
    Dim dicOffsets As Object
    Dim lngMonth As Long
    Dim lngOffset As Long
    Dim strPriorWIY As String
    Dim vItem As Variant
    
    Set dicOffsets = CreateObject("scripting.dictionary")
    
    intFN = FreeFile
    Open "C:\Users\Mark\Downloads\PeriodIssueData-Out.txt" For Input As #intFN
    strData = Input(LOF(intFN), intFN)
    Close

    Application.ScreenUpdating = False
    ActiveSheet.Range(Cells(1, 1), Cells(1, 2)).Value = Array("Warehouse", "Item")
    Set rngTgt = ActiveSheet.Cells(2, 1)

    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "Warehouse[^:]*:\s*(\S[^\r]*),\r\nItem[^:]*:\s*(\S[^\r]*),\r\nYear[^:]*:\s*(\d{4})((?:.|\n)*?)(?:\|\,)"

    If oRE.test(strData) Then
        Set oMatches = oRE.Execute(strData)
        lngOffset = 2
        For Each oM In oMatches     'inspect the years
            If dicOffsets.exists(oM.submatches(0) & "_" & oM.submatches(1) & "_" & oM.submatches(2) & "_1") Then
            Else
                For lngMonth = 1 To 12
                    dicOffsets(oM.submatches(0) & "_" & oM.submatches(1) & "_" & oM.submatches(2) & "_" & lngMonth) = lngOffset
                    ActiveSheet.Cells(1, lngOffset + 1).Value = oM.submatches(2) & "_" & lngMonth
                    lngOffset = lngOffset + 1
                Next
            End If
        Next
        strPriorWIY = dicOffsets.keys()(0)  'get first key
        'strip off the month and the year, leaving only the Whse/Item key
        strPriorWIY = Left(strPriorWIY, InStrRev(strPriorWIY, "_") - 1)
        strPriorWIY = Left(strPriorWIY, InStrRev(strPriorWIY, "_") - 1)
        
        oRE.Pattern = "\n\s*(\d+)\s+\|\s+(\d[^\s]*)\s"  'for month parsing
        For Each oM In oMatches
            With oM
                If strPriorWIY = .submatches(0) & "_" & .submatches(1) Then
                Else    'move rngTgt down to new row for different Whse/Item key
                    Set rngTgt = rngTgt.Offset(1)
                    strPriorWIY = .submatches(0) & "_" & .submatches(1)
                End If
                
                Set oMatches_Month = oRE.Execute(.submatches(3))
                rngTgt.Value = .submatches(0)
                rngTgt.Offset(0, 1).Value = .submatches(1)
                For Each oM_Month In oMatches_Month
                    rngTgt.Offset(0, dicOffsets(.submatches(0) & "_" & .submatches(1) & "_" & .submatches(2) & "_" & oM_Month.submatches(0))).Value = oM_Month.submatches(1)
                Next
            End With
        Next
    End If
    Application.ScreenUpdating = True
End Sub

Open in new window

produces this transformation into the active sheet
Warehouse	Item                                                                       	2014_1	2014_2	2014_3	2014_4	2014_5	2014_6	2014_7	2014_8	2014_9	2014_10	2014_11	2014_12	2015_1	2015_2	2015_3	2015_4	2015_5	2015_6	2015_7	2015_8	2015_9	2015_10	2015_11	2015_12
12345 XXXXX	1917009989                                  Opt 2000 S100 wired inner ass								2	8	6	5	3	2	6	1									

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary CroxfordOperations Support AnalystAuthor Commented:
Aikimark, This does precisely what I need - sorry not have replied before now but been on vacation. I appreciate that you've put a lot of effort into this and am really grateful but I'm not familiar with some of the things you are doing here and don't understand how the code is working, would you explain it for me please.

Thank you
0
aikimarkCommented:
* I use the regular expression object to parse the file data into "warehouse" chunks
* I iterate through the chunks

* I populate a dictionary object with unique (warehouse, item, year, month) keys with the data= worksheet column/offset
* I populate the row 1 of the worksheet with year_month values for each unique year encountered
* a new pattern is set for the regexp object, which will parse the chunks for monthly data
* I iterate through the chunks again

* the chunk's (warehouse, item, year) data is combined with the month value to get the column/offset
* the month amount is placed into the current row's correct column
Note: when the (warehouse, item, year) value changes, a new target row is set
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.