change rows and columns

[hendrikbez]

Do not know if this is possible, I have to change a worksheet.
On the worksheet, I have 500+ roes and 33 columns
on row 2 I have 8 columns with words that have the same meaning.

Now how on earth can I do the following for each row with it columns

I want to change it to 3 columns, (on column 1) it must go from 1 -to 700 (on column 2) the words ( and on column 3) group number. for row 1 and all it columns, the number must be 1.
but now because of this, the rows will be more, when we start a row 20 with all it columns

The new columns must be wordpk, word and groupfk

here is a screenshot of the first few rows and columns.

2019-08-13 13_18_19-Window.jpg

It should look like this ( there is on a few rows, the same words)

2019-08-13 13_25_22-Window.jpg

[Kaper]

From the description it seems that some VBA code would be the best solution for you, but ... probably nobody will write it just based on a screenshot. So please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Moreover, go to your profile page https://www.excelforum.com/profile.php?do=editprofile and update information on Excel version you are using. Some solutions may be your excel version dependant.

[hendrikbez]

I have added a file to explain what I need, on sheet 1(before) and sheet2 (after) is what I need to do.

On the worksheet, I have 500+ roes and 33 columns
on row 2 I have 8 columns with words that have the same meaning.

Now how on earth can I do the following for each row with it columns

I want to change it to 3 columns, (on column 1) it must go from 1 -to 700 (on column 2) the words ( and on column 3) group number. for row 1 and all it columns, the number must be 1.
but now because of this, the rows will be more, when we start a row 20 with all it columns

The new columns must be wordpk, word and groupfk

here is a screenshot of the first few rows and columns.

[Kaper]

Examining your sample file, it seems that you do not want to:

do the following for each row with it columns

but rather do it for every third row (see screenshot). If that is the case, use the below macro (see also attached file).

Please Login or Register  to view this content.

If it's not the case, be more specyfic, why in "After" sheet you placed

aä
aälawa

in rows 10 and 11

[hendrikbez]

Thank you, sorry for only replay now, but we have a death in the family today.

I need it to be every row, why I have put aä and aälawa in after, because the are in the same row, I know in the first few rows, there are the same words, but the rest of them I need all rows to be on after.
in after with the same

[Kaper]

Sorry, somehow I cannot understand you.
If you need every line to be processed just change:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

If you do not want duplicates - it is also possible, but would require different approach, and whole code shall be rewritten.

[hendrikbez]

Sorry, somehow I cannot understand you.
If you need every line to be processed just change:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

If you do not want duplicates - it is also possible, but would require different approach, and whole code shall be rewritten.

Thank you Kaper it worked like I want it.

[Kaper]

Glad to hear so, and thanks for reputation and for marking thread solved.

BTW: Use of "Reply With Quote" is rarely needed - mostly if you answer to one of earlier posts, but not the last one. Last one is visible just above the answer, so really no need to quote it.
See section "Forum Guidelines" in https://www.excelforum.com/forum-rul...rum-rules.html (the guidelines are located below the rules, just below the middle of the page)