Forum Discussion

Costan08's avatar
Costan08
Copper Contributor
Sep 21, 2022
Solved

Expanding range stated in same cell.

Hello everybody,   I have a question regarding a data table imported from a PDF file.    I have a product catalogue where a range of codes are 'grouped' in a single cell, expressed as 'Lowest num...
excel
Formulas and Functions
Macros and VBA
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Sep 22, 2022

    Costan08 Perhaps the attached file is what you want. It allows for the five code structures that I mentioned earlier. It seems a rather clumsy approach, though.

     

    The combined tables from the PDF contained 2238 line-items. The query produces 14648 line-items (216 more than in my previous attempt). Difficult to be sure that this one is complete now. Check it thoroughly please!

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
Sep 22, 2022

DexterG_III Nice, but unfortunately, the color codes do have non-continues character sequences. I noticed 5 different alpha-numeric structures:

n, nCn, Cn, CnC and CnCn where n stands for a number (or number sequence) and C stands for a character (or character sequence).

So, you will find codes like A90000 and AA229A just to give an example.

DexterG_III's avatar
DexterG_III
Iron Contributor
Sep 22, 2022

Riny_van_Eekelen So all this time my hoping the data would be simple enough to support my formula has been a failed tactic.  Reality sucks.  lol.  

 

Sincerely though, thanks for confirming.  Is there a max number of non-contiguous possibilities?  Is it possible to have CnCnC or more?  

 

Dexter

 

 

  • DexterG_III's avatar
    DexterG_III
    Iron Contributor
    Sep 22, 2022

    Riny_van_Eekelen I unfortunately closed that workbook without option to recover and need to sleep soon, but I do believe it's possible to

    1. Add two more columns directly after line 3 of the code above and use Text.Range in combination with the value from DG3_PositionOf to parse the remaining portion (after the first text character(s) were recognized) of both Range colori.1 & Custom - Copy .
    2. Add another column & use List Difference comparing the two new columns to return the second set of non contiguous characters
    3. Insert another column to return the position of the 2nd set of non-contiguous characters and add that to the original DG3_PositionOf value
    4.  After the DG5RebuildAplpha step, use the same logic to insert the 2nd set of non-contiguous characters. 

    I admit, it's not clean and there's likely a much more elegant solution but I do believe that would work in a pinch.  

     

    Sorry I'm not able to provide the actual code now.  

     

    I'll check in tomorrow and provide if this thread remains unresolved by then.  

Resources