Posts by kiler40

kiler40 · Apr 3rd 2017

Re: Create 2 columns for average in PIVOT tabel

Hi KjBox,
Thanks for the reply.
Unfortunately i have pivot from pivot.
And AVERAGE is not calculating correctly.
Because i have value split in main data i need one pivot to sum all time from same criteria. And second pivot is makein average of the sum.

I want to create table that is flexible to filter people, so i can take out average between some of them.

Hope it is not too complex.

kiler40 · Apr 3rd 2017

Dear Forum users,
I have a pivot table that is showing average time related value per one person in group of persons.
Now i want to add second column to show total average. but on every row. Purpose is to create chart with average per person as bar and total average as a line.

Hope my explanation is not to messy.

Thank you in advanced.

kiler40 · Sep 9th 2016

Re: VBA - Create multy filter from criteria in different table and copy the result

Hello Everybody
I have managed my self. not so neat solution but anyhow doing the job
Moderators can erase the topic. Thank you.

kiler40 · Sep 7th 2016

Dear Excel Guru Friends,
i hope anybody can help me with some automation. I`m really bad in coding but i have some super basic understanding.
So here is the thing
------------------------
Input:
in the attached file i have some sample data. in sheet "combinations" i have the initial data marked with the "start" shape. Filter is aplied to the heather of the "start" table.
In column AF there is "user module" info that is copied to the sheet "check" and all duplicates are removed. with the "RUN" button all combinations between them are created.

Output:
in the "combinations" sheet a filter must be applied column in column AF. this filter must be a combination of all values in columns E,F,G... and so one in sheet "check".
(in D column in sheets "check" there is the end combination of the filter)
after filter is applied in sheet "combinations" all visible rows must be copied and pasted. No matter if it is going to be in the same sheet or other. Before the paste there need be one cell in column X containing the name of the "master cell.value" in column X plus variant number, but in letters. Variant number and letters are in sheet "letters" Example: "master" connector name: Z142*1-L result Z142*1-L-A; Z142*1-L-B;.... etc
-------------------------
From how i see it is go row by row in "check"; recognize FIRST input variants in "check" table; go to "combinations"; filter combination in column AF, copy value from cell "X2" + number/NAME of variant (start 1), copy visible rows with no header, paste somewhere, clear filter, go to "check" -
SECOND input variant......

Problems:

filter multiple criteria;
create "X2"+name of variant. X2 is not visible all the time...;
paste at the end of previous variant

I really don`t think this is hard to be done, but my skills are really minimal (the combination creation macro is also coming from this forum Thank you people!)

Hope you can support me in this topic

Cheers,
Andrew

forum.ozgrid.com/index.php?attachment/70144/

kiler40 · Jul 15th 2016

Re: combine all CSV files in a folder to one file with some rules

Hi there
really nice job. Thanks
KjBox it is saying me "subscript out of range"

MrRedli it says that cannot find the workbook.

And all - sorry for the heather/header mistake. in my first post

kiler40 · Jul 15th 2016

Re: combine all CSV files in a folder to one file with some rules

It can be anything. Lets say C:\combine\
It contains only the files and all files need be combined.
But in this cell "B1" that i mentioned in the first post, there is one name that need be in column A in front of every row that is in the combined file to identify where the data is coming.

kiler40 · Jul 15th 2016

Re: combine all CSV files in a folder to one file with some rules

Because, I don`t know how to call it maybe title rows ?
Recording macro is not well working for me, because i`m really poor in coding.
I will understand how to copy the table, but then i need to do this for all files in a directory (800+)
and opening one by one....

kiler40 · Jul 13th 2016

Dear Forum Masters,
Can somebody please help me with combing some (not defined number) of files with same structure into one file removing the "heather" information.

all files are CSV with " ; " used as separator.
In all files, row 12 is the heather of the tables and until row 12 is the "heather" of the file. In this "heather" in cell B1 (after splitting the csv) is particular name/value. This value is important for me.

What i need is to have one file that combines everything from row 12 to the end of the file,
but put in column A in the combined files next to each row from the original file, the value of the B1 cell.

Can somebody please help me with some macro ?

Thanks in advance forum.ozgrid.com/index.php?attachment/69667/

Attached is 2 sample files.

Thanks in advance for the help

Kind regards,
Andrei

kiler40 · Jun 23rd 2016

Re: Color cells by VBA

Hello There. I have tried this code, but it is still the same.
I have left screen updating True and i can see that it is changing every cell really slow.

kiler40 · Jun 22nd 2016

Dear Forum Members,
i`m using a code do color cells based on value of the cell. But from some time started to work (nothing was changed in the code?!?!?) really slow.
Basically it reads value of a cell in column E of a sheet and the cell next to it gets colour equal to value.
For example "R" = RED, "R/W" = RED fill WHITE side border

My problem is that it is colouring thousands of rows and it is waiting for soo long.

Can anybody please tell me what is wrong in this code?
I am no big expert in coding. This code was done by a former colleague of me who has long ago left the company.

Code

Sub colorwires()
Dim currentcircuit As String
Dim circuitcolor() As String
Dim rowcount As Integer
Dim currentrow As Integer
Dim color As String
Dim secondcolor As String
Dim whitetext As Boolean






Sheets("input").Activate
Sheets("input").Range("F:F").Select
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Sheets("input").Range("E1").Select
whitetext = False
Range(Selection, Selection.End(xlDown)).Select




rowcount = Selection.Rows.Count
currentrow = 2
For currentrow = 2 To rowcount
whitetext = False
currentcircuit = Cells(currentrow, 5).Value


   circuitcolor() = Split(currentcircuit, "/", -1)
        
        Select Case circuitcolor(0)
            Case "L"
                color = 15773696
                whitetext = True
            Case "C"
                color = 11397625
            Case "Y"
                color = 65535
            Case "BR"
                whitetext = True
                color = 3368601
            Case "B"
                whitetext = True
                color = 526344
            Case "O"
                color = 39423
            Case "R"
                whitetext = True
                color = 255
            Case "P"
                color = 16764159
            Case "G"
                whitetext = True
                color = 5287936
            Case "V"
                color = 13369497
            Case "GY"
            whitetext = True
                color = 12632256
            Case Else
                color = 16777215
            End Select
         
         secondcolor = 2000
        If InStr(1, currentcircuit, "/", vbBinaryCompare) > 0 Then
        
        Select Case circuitcolor(1)
            Case "L"
                secondcolor = 15773696
            Case "C"
                secondcolor = 11397625
            Case "Y"
                secondcolor = 65535
            Case "BR"
                secondcolor = 3368601
            Case "B"
                secondcolor = 526344
            Case "O"
                secondcolor = 39423
            Case "R"
                secondcolor = 255
            Case "P"
                secondcolor = 16764159
            Case "G"
                secondcolor = 5287936
            Case "V"
                secondcolor = 13369497
            Case "GY"
                secondcolor = 12632256
            Case "W"
               secondcolor = 16777215
            End Select
            End If
            
        Cells(currentrow, 6).Interior.color = color
        If secondcolor <> 2000 Then
        Cells(currentrow, 6).Select
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        With Selection.Borders(xlDiagonalUp)
            .LineStyle = xlContinuous
            .color = secondcolor
            .Weight = xlThick
        End With
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .color = color
        End With
                End If
              
Next currentrow




End Sub

Display More

Whanks in advance for the help

kiler40 · Feb 12th 2016