Posts by lolstre.loster

Thank you again. It s perfect.

Br

Quote from lolstre.loster

Hello,

sorry i couldnt find the code, module 1 is empty

Hi, just found out why there was no macro in the file. Our IT security enabeld something in antivirus program that autonaticly delets macros from excell files. Itried it again on my own comp and it works great. Thanks again

Quote from dangelor

This worked after I removed all the notes in your sample file...

Code

Sub Problem1()
    Dim rg As Range
    Dim v As Variant, c As Variant
    Dim i As Long, lRow As Long
    
    Set rg = ThisWorkbook.Worksheets(1).Cells(1, 1).CurrentRegion
    Set rg = rg.Offset(1).Resize(rg.Rows.Count - 1)
    
    'get series numbers
    v = rg.Columns(2)
    With CreateObject("Scripting.Dictionary") 'Jindon
        For Each c In v
            .Item(c) = Empty
        Next
        c = .keys
    End With
    
    With ThisWorkbook.Worksheets(2)
        For Each v In c
            lRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
            For i = 1 To rg.Rows.Count
                If rg.Cells(i, 2).Value = v Then
                    rg.Cells(i, 1).Resize(, 4).Copy .Cells(lRow, 1)
                    rg.Cells(i, 5).Resize(, 9).Copy .Cells(lRow, .Columns.Count).End(xlToLeft).Offset(, 1)
                End If
            Next i
        Next v
    End With
End Sub

Display More

hi, the code works great but it is missing the header in first row. Can you please change it so the header would be included.

Thank you

Br

Quote from GCExcel

Hello,

Here's a code that should help you with this.

The code is in Module1, output will go in sheet called "Output"

Note: change the code sheet name as required.

Display More

Hello,

sorry i couldnt find the code, module 1 is empty

Thank you both. I will try it tomorow when i will be back in office.

Br

Hi

can i please get a little help. I would like to get multiple data with header (wich should be taged »name + 1,2,3,etc…)in one row based on an ID wich is »Series« and then sorted on »Name of component batch« and its matching »component of batch«

Guess this is only doable in VBA

I made a yellow spreadsheet of current state (this is the form in wich i get data), and blue spreadsheet desired look (this is how i would like it to look with macro).

Thank you in advance

BR

I solved the problem. Thank you for help

Hi, this does not work well. It gets almost all calculation wrong. Please see atached woorkbook for details

Hi,

I would ask for a little help. I would need a function that would sum and product numbers if the required data is inserted, and if data is partial it would return blank field. I tried aggregate function and did not do well because if i have etc. 4 data in original and in row only 3 data it sums that 3 that shuld be blank. I have a attached a workbook below for better understanding. Thank you
Bs

can admin delete this post? i would send him p.m., but i dont know who the admin is.
Reason: I have to redo the content and rephrase the question

Hi
can i please get a little help. I would like to get multiple data with header (wich should be taged »name + 1,2,3,etc…)in one row based on an ID wich is »Series« and then alphabeticly sorted on »Name of component batch« and its matching »component of batch«

Guess this is only doable in VBA

I made a spreadsheet of current state (this is the form in wich i get data), and desired look (this is how i would like it to look with macro).

Thank you in advance

it works just fine. Thank you.

Thanks for that.

I have one hopefully last question.

If i have the first set of data in column A from cell A2:A18, then empty space, next value is in the second set that begins lets say with A27:A37, the third set from A51:A57, etc..

Is it possible that the first value of second set (A27 ) is written right after the last set that ends on cell A18 whit all other values. Then the same thing goes for the third set which begins with A51, etc.

The same goes for column D and G

For better presenation The desired table is in column T of the attached file

one more thing. If i delete the first row (before column titles), i dont get the same order. Also is it possible that when you write the first formula and hit enter, you dont have to drag it down to get results in other cells, but you could only double click it and the values would go down. Its not a problem if i have only few rows, it becomes complicated if there are few 1000 rows. Please see the attachement "problem_updated"

Thank you

it works like a charm. Thansk a lot

Hi,
Can please someone help. I have a problem how to convert multiple rows and columns whith their belonging values, into two columns. Also if i add another row with its value under the last one, it should be also copied in the new table, at the same position. Please see attached file for better understanding. I wold preffer excell formula over the VBA. I tried something with »OFFSET« but it didnt work.

Thank you very much in addvance

cheers

Thank you. Both codes do the job

Hello

As I tried to explain very short in the title. I would like to delete rows that has duplicates "column Material", but still keep the entire row with higest number from a cell in the "column Version"
I have made an excell document (check the atachement i made) with two sheets, for better visualization. I would like that table looks like in the green sheet "desired_table". The data is in first sheet "Original Table"

In the original file i have apx. a few thousand rows

Macro would be great

Many thanks

Re: transpose nad write values from several columns in one

hello,
awsome, thank you very much. It couldnt be better.

Re: transpose nad write values from several columns in one

Macro please