Combining row and column heading according to selected cell

[Oxco]

Hi all,

ı could not find any formulas or example vba, so i wanted to ask you.

I want to combine the column and the row headings according to selected cell. You can understand easily what I meant.

Thanks again.

[Mumps1]

Place the following code into the code module for your worksheet "Sayfa1" (not into a regular module). Just click on the desired cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("C:C,E:E,G:G,I:I,K:K,M:M,O:O,Q:Q")) Is Nothing Then Exit Sub
    Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = Cells(Target.Row, 1) & "-" & Cells(1, Target.Column)
    Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = Target
End Sub

[Oxco]

Thanks for helping but I could not run the code. Most probably I could not process what you told me. Can you place your code into what you told me?
Or may be I could not tell what I want. When I run the code,the matris should be converted into 2 columns. First one is the merging the column heading and the row heading of the intersection of that row and that column, and the value should be written in the second column. Example of what I meant can be seen

[Mumps1]

Hi Oxco. Have a look at this attachment. Just click on any cell in the 'S' columns. If this is not what you wanted, please provide a more detailed explanation.

[Oxco]

Yes, actually it is done. But is it possible when I click the "run" button of the macro, all cells of the matris converted into columns?

Thanks again

[Mumps1]

Hi Oxco. I just realized that some of the cells in the 'S' column are blank. Please replace the old code with the code below. This revised code ignores the blank cells.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("C:C,E:E,G:G,I:I,K:K,M:M,O:O,Q:Q")) Is Nothing Then Exit Sub
    If Target <> "" Then
        Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = Cells(Target.Row, 1) & "-" & Cells(1, Target.Column)
        Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = Target
    End If
End Sub

I'm not sure what you mean when you say

when I click the "run" button of the macro, all cells of the matris converted into columns?

Do you have a button that runs another macro? Which cells do you want converted into columns?

[Oxco]

What I want is; when I click on any button we(you) formed, all of the cells('S' columns) of that matris are placed into columns. Withoutclicking any cell.

When I run the macro, columns in the second sheet named "to be formed like that" will form automatically.

[Mumps1]

Try the attached file. If I understood you correctly, you want column B in sheet "Sayfa2" to be updated automatically when you change the values in the 'S' columns in sheet "Sayfa1". I have done this by simply adding formulas in "Sayfa2" linking them to "Sayfa1". If this is not what you want, then I will need more clarification.

[Oxco]

You try a lot, thanks but again it is not my aim.

convert.jpg like that

[Mumps1]

Hi Oxco. Please try the attached file. I have added a button on your first sheet which when you click it, should copy the matrix to your second sheet. I have also unmerged the column headings because merged cell most often cause problems for Excel. You should avoid using merged cells if at all possible. Please let me know if this works for you.

[Oxco]

Yes, that's what I want. I'm really really thankful to you. =)

[Mumps1]

It took a little time but we finally got there! I'm glad it worked out.

[eric.oc]

example.xlsxScreen Shot 2013-04-27 at 9.02.18 AM.pngFirst of all, I need to apologize in advance for my ignorance, but I know NOTHING about macros. I am usually pretty good at getting formulas to do what I need, but in this case I have not been successful. Second, to be quite honest I am not entirely sure how to describe what it is I am trying to accomplish? But, here it goes anyway. I have an excel sheet containing one column of IDs and a second column of values that need to be associated to the first column. The problem is that the IDs in column A contain duplicates, which is okay because one ID can qualify for multiple values. What I need is to have a third column pull back the unique id, and a fourth column pull back a semi-colon delimited list of all of the values the id qualifies for. Hopefully the attached image makes sense?

[Mumps1]

Hi Eric. Forum rules state that you shouldn't post your question in someone else's thread. Please start your own thread and post the question there.