I have data which consists of a row with an identifier and several columns
which are options for the identifier that contain an X when it's selected. It
looks like this

number option1 option2 option3
1234 X X
2345 X X

What I'm trying to get is a single column of

1234-option1
1234-option2
2345-option2
2345-option3

I've tried to use transpose and concatenate but it's a large spreadsheet so
it's still very manual. Any ideas or macros that would help me automate this
process would be VERY appreciated.

One way to try ..

Assuming the source table below is in A1:D3

number option1 option2 option3
1234 X X
2345 X X
etc

Using empty cols to the right of the data

Put in E2: =IF(B2="X",$A2&"-"&B$1,"")
Copy E2 across 3* cols to G2
(*3 cols as there are 3 option cols)

Put in H2:
=OFFSET($E$2,INT((ROWS($A$1:A1)-1)/3),MOD(ROWS($A$1:A1)-1,3))

(The "3" in the OFFSET formula is equal to the number of option cols)

Select E2:H2, fill down until the last row of data in cols A to D

Put a label into H1, do a Data Filter Autofilter on it
& select: "(NonBlanks)" from the droplist in H1

Now just select and right-click copy all the filtered cells in col H
(those are the desired results) and then paste it elsewhere, say, in col A
in another sheet

Adapt to suit ..

For example, if there are say, 5 option cols altogether in cols B to F,

Put in G2: =IF(B2="X",$A2&"-"&B$1,"")
Copy G2 across 5 cols to K2

Put in L2:
=OFFSET($G$2,INT((ROWS($A$1:A1)-1)/5),MOD(ROWS($A$1:A1)-1,5))

Select G2:L2 and fill down. Rest of steps similar.

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Lynn Bales" wrote in message
...
I have data which consists of a row with an identifier and several columns
which are options for the identifier that contain an X when it's selected.
It
looks like this


What I'm trying to get is a single column of

1234-option1
1234-option2
2345-option2
2345-option3

I've tried to use transpose and concatenate but it's a large spreadsheet
so
it's still very manual. Any ideas or macros that would help me automate
this
process would be VERY appreciated.

Max,

This TOTALLY worked out. Is there a way to automate this further because I
have several sheets like this where the columns and rows change from workbook
to workbook. Like is there a way to macro this so I can enter the number of
rows and where the columns are? Sorry to be a big pain, this will just make
sorting out the unique entries SO much easier.

BTW, this workbook I was working on was 34 columns and 523 rows! The example
was just to get the feel for how it could work.

Thanks so much!

Lynn

"Max" wrote:

One way to try ..

Assuming the source table below is in A1:D3

number option1 option2 option3
1234 X X
2345 X X
etc

Using empty cols to the right of the data

Put in E2: =IF(B2="X",$A2&"-"&B$1,"")
Copy E2 across 3* cols to G2
(*3 cols as there are 3 option cols)

Put in H2:
=OFFSET($E$2,INT((ROWS($A$1:A1)-1)/3),MOD(ROWS($A$1:A1)-1,3))

(The "3" in the OFFSET formula is equal to the number of option cols)

Select E2:H2, fill down until the last row of data in cols A to D

Put a label into H1, do a Data Filter Autofilter on it
& select: "(NonBlanks)" from the droplist in H1

Now just select and right-click copy all the filtered cells in col H
(those are the desired results) and then paste it elsewhere, say, in col A
in another sheet

Adapt to suit ..

For example, if there are say, 5 option cols altogether in cols B to F,

Put in G2: =IF(B2="X",$A2&"-"&B$1,"")
Copy G2 across 5 cols to K2

Put in L2:
=OFFSET($G$2,INT((ROWS($A$1:A1)-1)/5),MOD(ROWS($A$1:A1)-1,5))

Select G2:L2 and fill down. Rest of steps similar.

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Lynn Bales" wrote in message
...
I have data which consists of a row with an identifier and several columns
which are options for the identifier that contain an X when it's selected.
It
looks like this


What I'm trying to get is a single column of

1234-option1
1234-option2
2345-option2
2345-option3

I've tried to use transpose and concatenate but it's a large spreadsheet
so
it's still very manual. Any ideas or macros that would help me automate
this
process would be VERY appreciated.

Glad it worked for you, Lynn !

.. Is there a way to automate this further ...
Not by me, I'm afraid. I've reached my incompetence level <g.

Hang around awhile to see if any of the other experienced folks versed in
vba were to step in here with something for you. Alternatively, you may
also wish to try a fresh post in .programming. All the best !
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Lynn Bales" wrote in message
...
Max,

This TOTALLY worked out. the columns and rows change from workbook
to workbook. Like is there a way to macro this so I can enter the number
of
rows and where the columns are? Sorry to be a big pain, this will just
make
sorting out the unique entries SO much easier.

BTW, this workbook I was working on was 34 columns and 523 rows! The
example
was just to get the feel for how it could work.

Thanks so much!

Lynn

You will have to change the range of myrange and the addres "H1" where the
reult is listed.
This macro worked for me.
Good luck


Sub test()
Dim myrange As Range
Set myrange = Range("a1:d5")
Range("H1").Activate
For Each cell In myrange
If cell.Value = "x" Then
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = myrange(cell.Row, 1).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = myrange(1, cell.Column).Value
ActiveCell.Offset(0, -1).Select
End If
Next
End Sub


--
Greetings from New Zealand
Bill K


"Lynn Bales" wrote in message
...
I have data which consists of a row with an identifier and several columns
which are options for the identifier that contain an X when it's selected.
It
looks like this

number option1 option2 option3
1234 X X
2345 X X

What I'm trying to get is a single column of

1234-option1
1234-option2
2345-option2
2345-option3

I've tried to use transpose and concatenate but it's a large spreadsheet
so
it's still very manual. Any ideas or macros that would help me automate
this
process would be VERY appreciated.

Worked perfectly Bill. Thank you.

I have another sheet that this would help me on but it contains columns
which have X in that I don't want. Is there a way to ask for the specific
columns or would that screw up the offset for it?

"Bill Kuunders" wrote:


You will have to change the range of myrange and the addres "H1" where the
reult is listed.
This macro worked for me.
Good luck


Sub test()
Dim myrange As Range
Set myrange = Range("a1:d5")
Range("H1").Activate
For Each cell In myrange
If cell.Value = "x" Then
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = myrange(cell.Row, 1).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = myrange(1, cell.Column).Value
ActiveCell.Offset(0, -1).Select
End If
Next
End Sub


--
Greetings from New Zealand
Bill K


"Lynn Bales" wrote in message
...
I have data which consists of a row with an identifier and several columns
which are options for the identifier that contain an X when it's selected.
It
looks like this

number option1 option2 option3
1234 X X
2345 X X

What I'm trying to get is a single column of

1234-option1
1234-option2
2345-option2
2345-option3

I've tried to use transpose and concatenate but it's a large spreadsheet
so
it's still very manual. Any ideas or macros that would help me automate
this
process would be VERY appreciated.

Bill,
I actually figured out my last question all by myself. Thanks for the code!
Lynn

"Bill Kuunders" wrote:


You will have to change the range of myrange and the addres "H1" where the
reult is listed.
This macro worked for me.
Good luck


Sub test()
Dim myrange As Range
Set myrange = Range("a1:d5")
Range("H1").Activate
For Each cell In myrange
If cell.Value = "x" Then
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = myrange(cell.Row, 1).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = myrange(1, cell.Column).Value
ActiveCell.Offset(0, -1).Select
End If
Next
End Sub


--
Greetings from New Zealand
Bill K


"Lynn Bales" wrote in message
...
I have data which consists of a row with an identifier and several columns
which are options for the identifier that contain an X when it's selected.
It
looks like this

number option1 option2 option3
1234 X X
2345 X X

What I'm trying to get is a single column of

1234-option1
1234-option2
2345-option2
2345-option3

I've tried to use transpose and concatenate but it's a large spreadsheet
so
it's still very manual. Any ideas or macros that would help me automate
this
process would be VERY appreciated.

Bill - One thing I can't figure out. In one of the sps I can use this on, I
need the two values concatenated into one column. So can you tell me what the
code would be to create a single column with what you already have written?
Thanks! Lynn

"Bill Kuunders" wrote:


You will have to change the range of myrange and the addres "H1" where the
reult is listed.
This macro worked for me.
Good luck


Sub test()
Dim myrange As Range
Set myrange = Range("a1:d5")
Range("H1").Activate
For Each cell In myrange
If cell.Value = "x" Then
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = myrange(cell.Row, 1).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = myrange(1, cell.Column).Value
ActiveCell.Offset(0, -1).Select
End If
Next
End Sub


--
Greetings from New Zealand
Bill K


"Lynn Bales" wrote in message
...
I have data which consists of a row with an identifier and several columns
which are options for the identifier that contain an X when it's selected.
It
looks like this

number option1 option2 option3
1234 X X
2345 X X

What I'm trying to get is a single column of

1234-option1
1234-option2
2345-option2
2345-option3

I've tried to use transpose and concatenate but it's a large spreadsheet
so
it's still very manual. Any ideas or macros that would help me automate
this
process would be VERY appreciated.

I assume you want that with a space between the two.
The macro is actually shorter ( :))

Have fun!!

Sub concat()
Dim myrange As Range
Set myrange = Range("a1:d5")
Range("h1").Activate
For Each cell In myrange
If cell.Value = "x" Then
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = myrange(cell.Row, 1).Value & " " & myrange(1,
cell.Column).Value
End If
Next
End Sub


Greetings from New Zealand
Bill K

"Lynn Bales" wrote in message
...
Bill - One thing I can't figure out. In one of the sps I can use this on,
I
need the two values concatenated into one column. So can you tell me what
the
code would be to create a single column with what you already have
written?
Thanks! Lynn

"Bill Kuunders" wrote:


You will have to change the range of myrange and the addres "H1" where
the
reult is listed.
This macro worked for me.
Good luck


Sub test()
Dim myrange As Range
Set myrange = Range("a1:d5")
Range("H1").Activate
For Each cell In myrange
If cell.Value = "x" Then
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = myrange(cell.Row, 1).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = myrange(1, cell.Column).Value
ActiveCell.Offset(0, -1).Select
End If
Next
End Sub


--
Greetings from New Zealand
Bill K


"Lynn Bales" wrote in message
...
I have data which consists of a row with an identifier and several
columns
which are options for the identifier that contain an X when it's
selected.
It
looks like this

number option1 option2 option3
1234 X X
2345 X X

What I'm trying to get is a single column of

1234-option1
1234-option2
2345-option2
2345-option3

I've tried to use transpose and concatenate but it's a large
spreadsheet
so
it's still very manual. Any ideas or macros that would help me automate
this
process would be VERY appreciated.

ActiveCell.Value = myrange(cell.Row, 1).Value & " " & myrange(1,
cell.Column).Value

by the way

the above two lines need to be on one line

with a space between myrange(1, and cell. Column

Regards
Bill K

"Bill Kuunders" wrote in message
...
I assume you want that with a space between the two.
The macro is actually shorter ( :))

Have fun!!

Sub concat()
Dim myrange As Range
Set myrange = Range("a1:d5")
Range("h1").Activate
For Each cell In myrange
If cell.Value = "x" Then
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = myrange(cell.Row, 1).Value & " " & myrange(1,
cell.Column).Value
End If
Next
End Sub


Greetings from New Zealand
Bill K

"Lynn Bales" wrote in message
...
Bill - One thing I can't figure out. In one of the sps I can use this on,
I
need the two values concatenated into one column. So can you tell me what
the
code would be to create a single column with what you already have
written?
Thanks! Lynn

"Bill Kuunders" wrote:


You will have to change the range of myrange and the addres "H1" where
the
reult is listed.
This macro worked for me.
Good luck


Sub test()
Dim myrange As Range
Set myrange = Range("a1:d5")
Range("H1").Activate
For Each cell In myrange
If cell.Value = "x" Then
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = myrange(cell.Row, 1).Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = myrange(1, cell.Column).Value
ActiveCell.Offset(0, -1).Select
End If
Next
End Sub


--
Greetings from New Zealand
Bill K


"Lynn Bales" wrote in message
...
I have data which consists of a row with an identifier and several
columns
which are options for the identifier that contain an X when it's
selected.
It
looks like this

number option1 option2 option3
1234 X X
2345 X X

What I'm trying to get is a single column of

1234-option1
1234-option2
2345-option2
2345-option3

I've tried to use transpose and concatenate but it's a large
spreadsheet
so
it's still very manual. Any ideas or macros that would help me
automate
this
process would be VERY appreciated.