Posts by jarko28

    Hi All,


    I have two files, a History file and a Forecast file. Each month, some of the column names in the files change based on a rolling monthly basis… for example, the forecast file has 4 static columns (sku, description, country, location) and then 28 months of forecasted sales data with column headers designated as the month 11/1/2021 through 1/1/2024….next month when I get the data file, the months will change to 12/1/2021 and end in 2/1/2024…the number of columns is always the same just the months roll over. The history file will be the same format as well with 24 months back rolling as the months go by…


    important to note that the files themselves are in very different formats (one comes horizontal, other is vertical, the sku and descriptions come in one column in one of the files)…basically I’m finding it very hard to manipulate the file without referring to the “hard coded” column names. The issue is the query breaks when I update it next month because the previous month’s column name disappears and causes a break in the code.


    I’m unable to create a power query to combine both files with dynamic column header names. I’ve tried a couple of options include creating lists but I keep getting errors so clearly I’m missing some critical part of the process…



    There are lots of videos online to solve this issue but I have not been able to implement a single one successfully without an error. Other options are not practical because they deal with changing just one columns, while my files have 30+ columns each.


    I am trying to implement a solution outlined in this video: YouTube video :

    External Content youtu.be
    Content embedded from external sources will not be displayed without your consent.
    Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.


    The idea is to replace the column names in the source file with the columns names in a mapping table.


    The issue I am facing is that I am basing my query not from an external file source but a named range in the same file. I have my mapping table but when I follow the steps, I get the following message: “Expression.Error: a cyclic reference was encountered during evaluation”

    ——


    Table.TransformColumnNames(tbl_Collab, each

    list.Accumulate(Table.ToRecords(Mapping) , _ ,

    (state, current)=> Text.Replace(Text.Upper(state),current

    [BEFORE], current[AFTER])))


    ——


    My named range and query name is “tbl_Collab” …if I understand the error correctly it’s because the step in the query can’t reference itself but not sure how to overcome this issue….when I replace the “tbl_Collab” in the code with the previous step, all that happens is the column names end up in upper case, so it seems to transform it but doesn’t look at the “after” column to look up and replace the values.


    Any help would be greatly appreciated.


    Thanks,


    Jerry

    Hi,


    What I am trying to do is this:


    I paste in a bunch of rows into a worksheet and they are in the wrong columns


    e.g:


    A1 = a
    B1 = b
    C1 = c
    D1 = d
    E1 = f


    Row 1= a,b,c,d,e,f


    what I need to happen is to have the values put into other columns in the same row for all rows that have data:


    result:


    cell A1 value is cut and pasted into cell D1
    cell B1 value is cut and pasted into cell A1
    cell C1 value is cut and pasted into cell B1
    cell D1 value is deleted
    cell E1 value is cut and pasted into cell C1


    Row 1 should have resulted in: d,a,b, ,c


    now if there is info in row 2, same thing needs to happen, and so on for all rows that are not blank


    any help would be appreciated!


    thanks.

    Hi all,


    I am struggling to find a solution to this problem, and I am hoping someone would be able to help me out:


    I have a list of items in column A that repeat a random number of times each with a corresponding date in column B and quantity in column C.


    What I am hoping for is a formula of some sort by where I could find the unique list of all items in the list in column G, the date that corresponds to the first negative record for that item in column H, and the value of the first negative record for that item in column I. If a non-negative item is not found, then the result should be the last record in the list for that item.


    I have included a sample with the desired result in the attachment.


    VBA solution would be ok as well, but I would like to see if a formula is possible at all...


    Thank you in advance.

    Hello,


    I have an issue that i have been searching for hours on the net for an cannot resolve.


    What I need to solve is the following:


    I have repeating values in column 1 which is the item number...Then in another column I have a running balance that corresponds to that item in column H and column I...I need to find the first negative value in column H and column I for that item and return the date in column B


    I have attached a sample worksheet and highlighted the values in red and yellow that are relevant for each item, and have included a before and after as well. I hope someone can help me out.


    thanks.

    Re: Font and format of listbox values based on condition


    thank you cytop.


    royuk, i checked out the treeview example, but i don't understand it at all, i am way too novice to create my own solution (i assume this is what it was meant to demonstrate, that you can code your own listview type of control?) thank you for taking the time to answer.

    Hello,


    I have a listbox that lists some items from a spreadsheet, but what I cannot figure out is how to have the font color of the items be RED if the value if negative, and default BLACK when positive...

    Would anyone know how to alter the code to accommodate this?



    Thanks in advance.

    Re: Error handling in vba or change to " if condition "


    Smallman, Holycow:


    i just figured it out based on what I need it to do...thank you again for your time and effort.


    Re: Error handling in vba or change to " if condition "


    Smallman,


    I have attached the file, when you click on sheet 1 on an item userform1 pops up.


    the item that is clicked is variable "y"


    it should look for "y" on sheet 2, if found, put value of the textbox and put it in column B in sheet 2 next to the found item...


    if not found, then it would populate both item and note in sheet 2 at the next empty row...


    thankyou and sorry for the confusion...


    the code is in the save button userform module