Copy from one cell to multiple cells in different column

[MrsMope]

Hello all,
I have a really ugly text file that I must get into a usable format in excel. First here is a sample of the data:

			BILLI	NG CODE	SUMMARY							
				FOR CITY								
		Bi	lled fro	m 05-01-	06 To 05-01-06	PAG	E 1					
[b]Grou	p    :	211111[/b]	Company ABC	
										6/23/2006 10:07	2006	
====	=======	======	========	========	==============	=================	==========	============	===============	=================	================	===========
						PREMIUM	ADJ	VOLUME	PREMIUM	TOTAL	TOTAL	CASH
CODE	DESCR	IPTION		COUNT	VOLUME	BILLED	COUNT	ADJUSTED	ADJUSTED	VOLUME	BILLED	POSTED
====	=======	======	========	========	==============	=================	==========	============	===============	=================	================	===========
21	EMPLOY	EE MED	ICAL FEE	3362	0	3,193.90	13	0	12.35	0	3,206.25	3,206.25
22	EMPLOY	EE DEN	TAL FEE	3362	0	7,396.40	13	0	28.6	0	7,425.00	7,425.00
58	COBRA/	HIPAA		3362	0	4,538.70	13	0	17.55	0	4,556.25	4,556.25
125	POSTAG	E FEE					0	0	7.64	0	7.64	7.64
CFN	NEG CO	MPOSIT	E FEE	3362	0	-15,129.00	13	0	-58.5	0	-15,187.50	-15,187.50
COM	COMPOS	ITE AD	MIN FEE	3362	0	15,129.00	13	0	58.5	0	15,187.50	15,187.50
====	=======	======	========	========	==============	=================	==========	============	===============	=================	================	===========
TOTA	LS				0	15,129.00		0	66.14	0	15,195.14	15,195.14

what I want to do is take the group number listed at the top and past it into column A until the group number changes. Any suggestions/ pointers?

Thanks,
RB

 

[SkipVought]

Hi RB,

It seems that you already did some kind of parsing as indicated by the spaces embedded in words like BILLING.

FIRST, import WITHOUT parsing -- ie all data is in column A.

Then your VBA logic will have to interpret the data to determine which KIND of row is being evaluated. Use the LEFT function for that.

Read from sheet 1 and write to sheet 2. In sheet 2, set up headings in row 1. Data in rows 2 and following.

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[MrsMope]

Skip,
That is the most fantastic idea! Thanks so much, I wouldn't have thought of that. I will try it and post any issues.

Thanks,
RB

 

[MrsMope]

Skip and all others,
The format I want the file to end up in is:

Group Number	Service Code	Sell Price
211111	21	3206.25
211111	22	7425
211111	58	4556.25
211111	125	7.64
211111	CFN	-15,187.50
211111	COM	15,187.50

So what I have so far is:

Public Sub CopyRows()

   Dim SourceSheet As Worksheet
   Dim TargetSheet As Worksheet
   Dim SourceRow As Long
   Dim TargetRow As Long
   Dim Index As Long
   
   Const StartRow = 2
   
   Set SourceSheet = Sheets("Input")
   Set TargetSheet = Sheets("Output")
   
   TargetRow = StartRow
   
   For SourceRow = StartRow To 65536
      If Left(Cells(SourceRow, 1), 5).Value = "Group" Then

Am I on the right track?

 

[SkipVought]

Your Loop

For SourceRow = StartRow To SourceSheet.Cells(SourceSheet.Cells.rows.count, 1).row
   With SourceSheet.Cells(SourceRow, 1)
      If .value Like "Group*" Then

      elseif .value like "*PREMIUM*" then

      elseif .value like "CODE*" then

      elseif .value like "TOTAL*" then

      elseif .value like "==*" then

      elseif UCASE(.value) like "*BILL*" then

      elseif UCASE(.value) like "*CITY*" then

      else
         'THIS IS DATA 

      end if
   End With
Next

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[MrsMope]

Thanks Skip,
I will try this out tonight and let you all know
!

Thanks,
RB

 

[MrsMope]

Skip and all others,
I have a question, here is what I have inserted into my loop:

 With SourceSheet.Cells(SourceRow, 1)
      If .Value Like "Group*" Then
[red]            TargetSheet.Cells(TargetRow, 1).Value = Mid(SourceSheet.Cells(SourceRow, 1).Value, 12, 6)[/red]
      ElseIf .Value Like "*PREMIUM*" Then

      ElseIf .Value Like "CODE*" Then

      ElseIf .Value Like "TOTAL*" Then

      ElseIf .Value Like "==*" Then

      ElseIf UCase(.Value) Like "*BILL*" Then

      ElseIf UCase(.Value) Like "*CITY*" Then
      ElseIf .Value Like "  *" Then
      

      Else
      [red]  TargetSheet.Cells(TargetRow, 2).Value = Left(SourceSheet.Cells(SourceRow, 1).Value, 3)[/red]

      End If
   End With
   TargetRow = TargetRow + 1
Next SourceRow

and here is what I am getting on my output sheet:

Group Number	Service Code	Sell Price
		


240603		
		
		



	21

My question is how do I code this so there is not a series of blank lines?

Thanks,
RB

 

[SkipVought]

TargetRow only gets incrimented when you're done evaluating all the data for one output row.

I'd make a variable for Group, since that's going on each data row. When you're on a Group input row, there's gonna be NO OUTPUT ROW INCRIMENTING.

      If .Value Like "Group*" Then
            sGroup = Mid(SourceSheet.Cells(SourceRow, 1).Value, 12, 6)

Your incrimenting ONLY happens in DATA ROWS

      Else
      'insert group
        TargetSheet.Cells(TargetRow, 1).Value = sGroup 
        TargetSheet.Cells(TargetRow, 2).Value = Left(SourceSheet.Cells(SourceRow, 1).Value, 3)
      'also goes other data fields
        
        TargetRow = TargetRow + 1

Skip,

_{[red]Be Advised![/red] The only distinction between a bird with one wing and a bird with two, is merely...
a difference of A Pinion!}

 

[MrsMope]

Thanks Skip,
This is what I did:

  ElseIf .Value Like "" Then
      Else
        If TargetSheet.Cells(TargetRow, 1).Value = "" Or Null Then
                TargetSheet.Cells(TargetRow, 1).Value = TargetSheet.Cells((TargetRow - 1), 1).Value
                TargetSheet.Cells(TargetRow, 2).Value = Left(SourceSheet.Cells(SourceRow, 1).Value, 3)
        ElseIf TargetSheet.Cells((TargetRow - 1), 1).Value = "Group Number" Then
                TargetSheet.Cells(TargetRow, 2).Value = Left(SourceSheet.Cells(SourceRow, 1).Value, 3)
        Else
                TargetSheet.Cells(TargetRow, 1).Value = TargetSheet.Cells(TargetRow, 1).Value
                TargetSheet.Cells(TargetRow, 2).Value = Left(SourceSheet.Cells(SourceRow, 1).Value, 3)
        End If
        TargetRow = TargetRow + 1
      End If
   End With
next sourceRow

Now my next step is to split out the dollars.....