?making 4 columns 1 w/ refrences?

Originally Posted by rockstar

I have a complex situation with my data and I would like to make a quick list of information for geographical plotting, here is my problem.

Data example:
--------------|--------------INCIDENT---------|----------LOCALE
DATE|-PERSON|-ONE--|-TWO--|THREE|FOUR-|-ONE--|-TWO--|THREE|FOUR
1/1--|-mr. a--|-theft--|-murder|------|------|1n2w--|-1n4w-|------|
1/1--|-mr. b--|-assault|-j-walk-|--dui-|------|2n1w--|-2n2w-|-2n2w|
1/2--|-mr. b--|-robbery|-------|------|------|1n1w--|-------|------|
1/3--|-mr. c--|-j-walk-|--------|------|------|2n3w-|-------|------|

I have more information that goes with each one but this illustrates my problem. each row represents an individuals work load for one instance on a given day so the information had to be recorded this way. but now I would like to extract the columns on the incidents with the locales so they can be plotted geographically, ie. :

theft----| 1n2w
murder--| 1n4w
assault--| 2n1w
j-walk --| 2n2w
dui------| 2n2w
robbery-| 1n1w
j-walk---| 2n3w

is there any tool to do this within excel. Macro, function, PivotTable, anything?

Hi,

I doubt that anyone has understood what you are trying to do, a sample workbook might have been better than the somewhat disjointed display.

At a guess, I would say that you need a small macro that tests, for each row, columns C, D, E and F and for non-blanks adds a line showing C and Column+4 in columns A and B

Is this correct?
---

Originally Posted by rockstar

sorry about the confusion. You assume correctly. I have attached a sample datasheet.

OK - create a macro, and set the code as

Sub SetLines()
Dim iRow As Long, iLastRow As Long, iColumn As Long
iLastRow = ActiveSheet.Range("A65536").End(xlUp).Row
For iRow = iLastRow To 2 Step -1
    For iColumn = 3 To 6
    If Not Cells(iRow, iColumn).Value = "" Then
        Rows(iRow + 1).EntireRow.Insert
        Cells(iRow + 1, 1).Value = Cells(iRow, iColumn).Value
        Cells(iRow + 1, 2).Value = Cells(iRow, iColumn + 4).Value
        End If
        Next
    Next
End Sub

you could then set the macro options to allow a Shortcut key (say CTRL/Shift/S) and run the macro by pressing ctrl/shift/s.

The macro does not check the sheet name, be carefull of destroying other sheets. The macro is not fully tested, try it out AFTER saving a copy of your data.

The macro does not clear the cells from which the data came, you may want to add

      Cells(iRow, iColumn).Value = ""
      Cells(iRow, iColumn + 4).Value = ""

immediately before the EndIf

hth
---

added

you might prefer to use

For iColumn = 6 To 3 Step -1

to sequence correctly.
---