Excel Formula / VBA

[Brianfree]

Hi, i have the following data which i need to resolve alternate products and stock..

Original Data

BASE NUMBER
ABC123
DEF456

Desired Results

BASE NUMBER, ITEM CODE, STOCK
ABC123, ABCD123-10, 50
ABC123, ABCD123-15, 7
DEF456, DEF456-3, 5
DEF456, DEF456-60, 7

I Have a speadsheet with the base numbers on one sheet, and the second sheet has a list of item codes and stock

I have already got a function to put the item codes and stock in to a cell next to the base number but I need to insert new rows and return the results..

Please can anyone help?

Kindest thanks
Brian

 

[SkipVought]

Hi,

Use a query to join the two tables.

 

[Brianfree]

Hi, I'm using match and index at the moment but this only returns one result... I need to return all results agains the base number but will need to duplicate the rows somehow..

 

[kjv1611]

When Skip says use a query, you can use MS Query to join the 2 spreadsheets just like you would in a database. To get there, create a new worksheet within the same workbook, and go to data -> From other sources -> from microsoft query. Then choose Excel files for the databas, click OK, then find your same Excel file (assuming you've already saved it before you begin this step), and go from there. You probably will end up needing to do the joins custom inside the query design tool rather than just straight returning the data to Excel.

Also, if you can't seem to find a way to get the ABCD123-10 reduced to just ABCD123 then, You can use a couple of functions in an Excel formula to add that first, then do the querying, and it'll possibly be simpler.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Actually, the more I think about it, if Item Code is a variant of Base Code plus something else, then you should be able to ONLY use the formula to get what you're after without a query. You won't need MATCH and INDEX, nor will you need VLOOKUP or anything of the like.

If you have your current values in columns A and B, I'd insert a new column for A, then use a nested formula setup to remove the "-10" portion of your Item Code in order to give you the Base Code. Then if need be, you can copy/paste special on Column A once it's right, and you'll just have the raw data you need.

The formulas I forget offhand, but you want to find the "-" character, and basically just get everything to the left of it. I've done it before in Excel formulas, VBA, SQL, and Access queries. So I'd have to go through the exercise to remember exactly what I did in an Excel formula.

If you prefer VBA, you could insert the column, then loop through the rows, and perhaps use a string array variable to chop off the extra piece.. well, you could do it without the split.. there are at least 2 ways in VBA to do it.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Brain,

Index & Match won't cut it.

Index & Match won't work the way your results are structured, when you have multiples!

 

[SkipVought]

pWe can assume, but no statement yet on this table's structure, that Sheet2 table is...

ITEM CODE, STOCK

So I'd guess the SQL might be something like...

SELECT [BASE NUMBER], [ITEM CODE], STOCK
FROM [Sheet1$], [Sheet2$]
WHERE [BASE NUMBER]=RIGHT([ITEM CODE],6)

Whatever your actual sheet names are, replace.

 

[Brianfree]

Thanks for all your reply - never thought about using queries... Will have a go ;-)

Many thanks

Brian

 

[Brianfree]

Hi, I have managed to write a function to return all the item codes into a new cell comma separated.. I also have some vbcode to split into new lines. All works fine apart it needs to be on the same sheet..

Is it possible for the code to use the values on one sheet and display the results on a new sheet?

here is my code...

Many thanks

BF

Sub SliceNDice()
    Dim objRegex As Object
    Dim X
    Dim Y
    Dim lngRow As Long
    Dim lngCnt As Long
    Dim tempArr() As String
    Dim strArr
    Set objRegex = CreateObject("vbscript.regexp")
    objRegex.Pattern = "^\s+(.+?)$"
     'Define the range to be analysed
    Set X = Sheets("Results")
    X = Range([a1], Cells(Rows.Count, "d").End(xlUp)).Value2
    ReDim Y(1 To 4, 1 To 1000)
    For lngRow = 1 To UBound(X, 1)
         'Split each string by ","
        tempArr = Split(X(lngRow, 4), ",")
        For Each strArr In tempArr
            lngCnt = lngCnt + 1
             'Add another 1000 records to resorted array every 1000 records
            If lngCnt Mod 1000 = 0 Then ReDim Preserve Y(1 To 4, 1 To lngCnt + 1000)
            Y(1, lngCnt) = X(lngRow, 1)
            Y(2, lngCnt) = X(lngRow, 2)
            Y(3, lngCnt) = X(lngRow, 3)
            Y(4, lngCnt) = objRegex.Replace(strArr, "$1")
        Next
    Next lngRow
     'Dump the re-ordered range to columns E:H
    [e1].Resize(lngCnt, 4).Value2 = Application.Transpose(Y)
    ActiveSheet.Range("E:H").RemoveDuplicates Columns:=Array(1, 2, 3, 4), _
        Header:=xlNo
End Sub