How do I make a unique entry

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
Click to expand...
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.


--
 
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
 
Glad it worked for you, Lynn !
.. Is there a way to automate this further ...
Click to expand...
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 !

--
 
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
 
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 - 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
 
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
 
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
 
Back
Top