help with data parsing

Hi,
I have data which is in the format below called "Before Data" and I am
trying to get it to "After Data" format. I can do some programming if given
some advice.
As you notice I am trying to repeat data from
ID Long Lat
1 9.5 4

where 4 is number of times value 9.5 is included in Value column of after
data.

Any help would be appreciated.

Before Data After Data
ID Long Lat ID Long Lat Value
1 9.5 4 1 9.5 4
2 -118.727 37.81862 2 -118.727 37.81862 9.5
3 -118.728 37.81875 3 -118.728 37.81875 9.5
4 -118.731 37.81907 4 -118.731 37.81907 9.5
5 -118.735 37.81995 5 -118.735 37.81995 9.5
6 10.5 3 6 10.5 3
7 -118.743 37.82292 7 -118.743 37.82292 10.5
8 -118.744 37.82338 8 -118.744 37.82338 10.5
9 -118.748 37.8261 9 -118.748 37.8261 10.5

Sorry this is how the data looks
Before Data
ID Long Lat
1 9.5 4
2 -118.727 37.81862
3 -118.728 37.81875
4 -118.731 37.81907
5 -118.735 37.81995
6 10.5 3
7 -118.743 37.82292
8 -118.744 37.82338
9 -118.748 37.8261



After Data
ID Long Lat Value
1 9.5 4
2 -118.727 37.81862 9.5
3 -118.728 37.81875 9.5
4 -118.731 37.81907 9.5
5 -118.735 37.81995 9.5
6 10.5 3
7 -118.743 37.82292 10.5
8 -118.744 37.82338 10.5
9 -118.748 37.8261 10.5



"Param Dhillon" <[email protected]> wrote in message
news:%[email protected]...
> Hi,
> I have data which is in the format below called "Before Data" and I am
> trying to get it to "After Data" format. I can do some programming if
given
> some advice.
> As you notice I am trying to repeat data from
> ID Long Lat
> 1 9.5 4
>
> where 4 is number of times value 9.5 is included in Value column of after
> data.
>
> Any help would be appreciated.
>
> Before Data After Data
> ID Long Lat ID Long Lat Value
> 1 9.5 4 1 9.5 4
> 2 -118.727 37.81862 2 -118.727 37.81862 9.5
> 3 -118.728 37.81875 3 -118.728 37.81875 9.5
> 4 -118.731 37.81907 4 -118.731 37.81907 9.5
> 5 -118.735 37.81995 5 -118.735 37.81995 9.5
> 6 10.5 3 6 10.5 3
> 7 -118.743 37.82292 7 -118.743 37.82292 10.5
> 8 -118.744 37.82338 8 -118.744 37.82338 10.5
> 9 -118.748 37.8261 9 -118.748 37.8261 10.5
>
>

Hi Param,
Is the data you present in the form of text is one long string or is it in
an Excel layout already? Will these values be repeated over 9 times for each
instance?
Thanks,

"Param Dhillon" wrote:

> Hi,
> I have data which is in the format below called "Before Data" and I am
> trying to get it to "After Data" format. I can do some programming if given
> some advice.
> As you notice I am trying to repeat data from
> ID Long Lat
> 1 9.5 4
>
> where 4 is number of times value 9.5 is included in Value column of after
> data.
>
> Any help would be appreciated.
>
> Before Data After Data
> ID Long Lat ID Long Lat Value
> 1 9.5 4 1 9.5 4
> 2 -118.727 37.81862 2 -118.727 37.81862 9.5
> 3 -118.728 37.81875 3 -118.728 37.81875 9.5
> 4 -118.731 37.81907 4 -118.731 37.81907 9.5
> 5 -118.735 37.81995 5 -118.735 37.81995 9.5
> 6 10.5 3 6 10.5 3
> 7 -118.743 37.82292 7 -118.743 37.82292 10.5
> 8 -118.744 37.82338 8 -118.744 37.82338 10.5
> 9 -118.748 37.8261 9 -118.748 37.8261 10.5
>
>
>

Hi David,
The data is allready in the excel format. If you look on my second
thread its little more clear. Actaully 9.5 value will be repeated 4 times
and the 10.5 value will be repeated 3 times in a new colum called value.

PLease look under "After Data" and

ID 1

and

ID 6

Thanks
Param

After Data
ID Long Lat Value
1 ,9.5 ,4
2 ,-118.727, 37.81862, 9.5
3 -118.728, 37.81875, 9.5
4 -118.731, 37.81907 ,9.5
5 -118.735, 37.81995 ,9.5
6, 10.5 ,3
7 -118.743, 37.82292, 10.5
8 -118.744, 37.82338, 10.5
9 -118.748 ,37.8261, 10.5

Sorry but I am still having a difficult time visioning what you have as data.
The below is in 3 columns in Excel. I accoplished this by copying the data
you had and using Data/Text to columns. This may not be what your data looks
like currently in Excel. Then the other question was will the groups be
repeated more than 9 times?
ID Log Lat
1 9.5 4
2 -118.727 37.81862
3 -118.728 37.81875
4 -118.731 37.81907
5 -118.735 37.81995
6 10.5 3
7 -118.743 37.82292
8 -118.744 37.82338
9 -118.748 37.8261

OR it could look like this? Where the "repeated" number is combined with the
number of repatitions?
ID Log Lat
1 9.5 4
2 -118.727 37.81862
3 -118.728 37.81875
4 -118.731 37.81907
5 -118.735 37.81995
6 10.5 3
7 -118.743 37.82292
8 -118.744 37.82338
9 -118.748 37.8261
Thanks,




"Param Dhillon" wrote:

> Sorry this is how the data looks
> Before Data
> ID Long Lat
> 1 9.5 4
> 2 -118.727 37.81862
> 3 -118.728 37.81875
> 4 -118.731 37.81907
> 5 -118.735 37.81995
> 6 10.5 3
> 7 -118.743 37.82292
> 8 -118.744 37.82338
> 9 -118.748 37.8261
>
>
>
> After Data
> ID Long Lat Value
> 1 9.5 4
> 2 -118.727 37.81862 9.5
> 3 -118.728 37.81875 9.5
> 4 -118.731 37.81907 9.5
> 5 -118.735 37.81995 9.5
> 6 10.5 3
> 7 -118.743 37.82292 10.5
> 8 -118.744 37.82338 10.5
> 9 -118.748 37.8261 10.5
>
>
>
> "Param Dhillon" <[email protected]> wrote in message
> news:%[email protected]...
> > Hi,
> > I have data which is in the format below called "Before Data" and I am
> > trying to get it to "After Data" format. I can do some programming if
> given
> > some advice.
> > As you notice I am trying to repeat data from
> > ID Long Lat
> > 1 9.5 4
> >
> > where 4 is number of times value 9.5 is included in Value column of after
> > data.
> >
> > Any help would be appreciated.
> >
> > Before Data After Data
> > ID Long Lat ID Long Lat Value
> > 1 9.5 4 1 9.5 4
> > 2 -118.727 37.81862 2 -118.727 37.81862 9.5
> > 3 -118.728 37.81875 3 -118.728 37.81875 9.5
> > 4 -118.731 37.81907 4 -118.731 37.81907 9.5
> > 5 -118.735 37.81995 5 -118.735 37.81995 9.5
> > 6 10.5 3 6 10.5 3
> > 7 -118.743 37.82292 7 -118.743 37.82292 10.5
> > 8 -118.744 37.82338 8 -118.744 37.82338 10.5
> > 9 -118.748 37.8261 9 -118.748 37.8261 10.5
> >
> >
>
>
>

'Start in cell D1
Sub Macro1()
Do Until ActiveCell.Offset(0, -2).Value = ""
ActiveCell.Offset(1, 0).Range("A1").Select
LongLength = Len(ActiveCell.Offset(0, -2).Value)
If LongLength = 0 Then End
If LongLength < 6 Then
RepeatNo = ActiveCell.Offset(0, -2).Value
RepeatTimes = ActiveCell.Offset(0, -1).Value
For Z = 1 To RepeatTimes
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Value = RepeatNo
Next Z
Z = 0
Else
End If
Loop
End Sub

ID Long Lat (D1)
1 9.5 4
2 -118.727 37.81862 9.5
3 -118.728 37.81875 9.5
4 -118.731 37.81907 9.5
5 -118.735 37.81995 9.5
6 10.5 3
7 -118.743 37.82292 10.5
8 -118.744 37.82338 10.5
9 -118.748 37.8261 10.5
Hope this helps.

"Param Dhillon" wrote:

> Hi David,
> The data is allready in the excel format. If you look on my second
> thread its little more clear. Actaully 9.5 value will be repeated 4 times
> and the 10.5 value will be repeated 3 times in a new colum called value.
>
> PLease look under "After Data" and
>
> ID 1
>
> and
>
> ID 6
>
> Thanks
> Param
>
> After Data
> ID Long Lat Value
> 1 ,9.5 ,4
> 2 ,-118.727, 37.81862, 9.5
> 3 -118.728, 37.81875, 9.5
> 4 -118.731, 37.81907 ,9.5
> 5 -118.735, 37.81995 ,9.5
> 6, 10.5 ,3
> 7 -118.743, 37.82292, 10.5
> 8 -118.744, 37.82338, 10.5
> 9 -118.748 ,37.8261, 10.5
>
>
>