Posts by jarko28

Hi,

Attached is the sample file with error in the query.

thanks.

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 :

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 all,

I am trying to transpose a list of values to rows based on a value in the first column. It is very hard to explain but I attached a sample file with the input and desired output. A formula solution would be best, however, if vba works then that would be ok too.

Thanks in advance for your help!

Re: put cell values in other cells on same sheet in active range

Hi,

I have attached a sample. I don't think i did a good job explaining. It is basically re-ordering columns in an order I would define without affecting to column headers.

Thanks.

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.

Re: Compare two string for similarity and give a score?

There is a Microsoft add in tht does exactly what you need I think:

http://www.microsoft.com/en-ca…oad/details.aspx?id=15011

Re: Find first negative value in column based on value in another column

Jindon,

I replaced :

With .Offset(.Rows.Count + 2)

with:

With Sheets("sheet2").Cells(1, 1)

and it seems to work perfect! thank you again...

Re: Find first negative value in column based on value in another column

Jindon,

this is super...i didn't realize this would be a vba solution...how would i edit this if i wanted this output on a new sheet and every time i click update, it would delete the old and replace with new?

Thank you in advance for your time!

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: Conditional Formats conflict - Better Formula needed

Great, glad it worked.

Re: Conditional Formats conflict - Better Formula needed

can't you just put the white formatting as the first rule and click the "stop if true" button? the rules will apply in order but stop if a condition is met...

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.

Re: Font and format of listbox values based on condition

cytop,

I am using excel 2010 64bit - list view does not seem to work in this version from my research...any other options you could see that would work?

Re: Font and format of listbox values based on condition

Thank you cytop. I will investigate the listview control.

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?

With UserForm1.ListBox4
            .ColumnCount = 5
            .ColumnWidths = "2 cm;3 cm;3 cm; 3 cm; 3 cm"
         For Each Val In a
            .AddItem
            .List(n, 0) = Val.Offset(0, 1): _
            .List(n, 1) = Format(Val.Offset(0, 9), "#,##0"): _
            .List(n, 2) = Format(Val.Offset(0, 6), "#,##0"): _
            .List(n, 3) = Format(Val.Offset(0, 7), "#,##0"): _
            .List(n, 4) = Format(Val.Offset(0, 8), "#,##0"): _
            n = n + 1
          Next
        End With

Thanks in advance.

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

great point smallman, i will make the adjustments as you mentioned. thanks again.

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.

Private Sub CommandButton1_Click()
    Dim r As Range
    Dim y As Variant
    y = ActiveCell
     
   On Error GoTo YoError
    
     rng1 = Sheets(2).Range("A1:A5000")
     
    Dim l As Long
     
    l = Application.WorksheetFunction.Match(y, rng1, 0)
    Sheets(2).Cells(l, 2) = TextBox1.Value
    
    Exit Sub
YoError:     Sheets(2).Range("A65536").End(xlUp)(2).Resize(, 2) = Array(y, TextBox1.Value)
End Sub

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

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

Smallman, I actually am very very naive to vba, so i did not know it was not designed for that purpose, or that it was an additional request...

sorry for the confusion.