?making 4 columns 1 w/ refrences?

[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?

[Bryan Hessey]

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?
---

[rockstar]

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

[Bryan Hessey]

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

OK - create a macro, and set the code as

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

immediately before the EndIf

hth
---

added

you might prefer to use

For iColumn = 6 To 3 Step -1

to sequence correctly.
---