Excel VBA - Manipulating Data - Adding Tittles to Data

[Randy11]

Have data imported from a text file that need to Manipulate into a clean data table.
In column A there are different account types MANUAL DEBITS & MANUAL CREDITS. The number of accounts involved varies The Account Types Remain the same. For futher downstream handling here is what I am looking to do. 1 Insert the account type at the front of each related data line. Also wanting a add Kinear to the Manual Debit Account type & Relic to Manual Credit Account type.

This Change to This
Column A Column A Column B
MANUAL DEBITS KINEAR MANUAL DEBITS 06666 9999999 06666 9999999 KINEAR MANUAL DEBITS 00123 2256925
00123 2256925 KINEAR MANUAL DEBITS 01111 2222222
01111 2222222 KINEAR MANUAL DEBITS 02222 1444444
02222 1444444 KINEAR MANUAL DEBITS 03333 1555555
03333 1555555 RELIC MANUAL CREDITS 04222 4777777
MANUAL CREDITS
04222 4777777

Really not clear on how to start on this. Ideas appreciated.

 

[SkipVought]

Hi,

It is not at all clear what the before is and what the after is.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Sorry Skip, everything shifted...

Before
Column A
MANUAL DEBITS
00123 2256925
00234 5555555
01111 2222222
02222 1444444
06666 9999999
MANUAL CREDITS
04222 4777777

After
Change to This
Column A is Acct Type | Column B is Acct Numbers
KINEAR MANUAL DEBITS | 00123 2256925
KINEAR MANUAL DEBITS | 00234 5555555
KINEAR MANUAL DEBITS | 01111 2222222
KINEAR MANUAL DEBITS | 02222 1444444
KINEAR MANUAL DEBITS | 06666 9999999
RELIC MANUAL CREDITS | 04222 4777777

 

[SkipVought]

So each text file has data for ONE Account Type Debit & Credit? Or is this all there is?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Yes, each worksheet data is imported to will have Manual Debits & Manual Credit a Work Types. The number of acct numbers will vary.

 

[SkipVought]

EACH???

That is troubling to me. What does that mean?

Please explain exactly what would happen in your workbook, if there were TWO worksheet data imports.

How do these imports occur?

Where would the data reside in your workbook?

Would you ALWAYS add KINEAR & RELIC to each import?

This is all very nebulous and pointless at this juncture.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Simple code is set up to import data into a number of worksheets & clean up some of the formatting issues etc. For the sake of this discussion will call them DataImport1, DataImport2, DataImport3. Am looking for this code to to work on Active worksheet at this time. May later apply this elsewhere or include in the import code if feasible. There is some user input that will occur once this peice of the code is run. Later code will consolidate a number of worksheets data & run other required tasks.
KINEAR & RELIC are going to be added to the active data worksheet DataImport1 as example. Other names will be added to other similar worksheets.

 

[SkipVought]

You do realize that having similar data in different locations can become a huge obstacle down the road. We see questions on a regular basis, asking how to consolidate, analyze & report such non-normalized data.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Please post your code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Without significant efforts to mask information am unable to do this.
Are you able to provide some assistance that will allow understanding of how to perform the task with the one specific worksheet. ImportData1.

 

[SkipVought]

Your VBA is not company information!

I am not asking for data. I am asking for CODE!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

Really just looking for a solution or some assistance with this peice

On ImpartData1 worksheet. Number of Acct numbers will vary. Account Types MANUAL DEBITS & MANUAL CREDITS will not vary in number.

Changing this
Before
Column A
MANUAL DEBITS
00123 2256925
00234 5555555
01111 2222222
02222 1444444
06666 9999999
MANUAL CREDITS
04222 4777777

to
After
Change to This
Column A is Acct Type | Column B is Acct Numbers
KINEAR MANUAL DEBITS | 00123 2256925
KINEAR MANUAL DEBITS | 00234 5555555
KINEAR MANUAL DEBITS | 01111 2222222
KINEAR MANUAL DEBITS | 02222 1444444
KINEAR MANUAL DEBITS | 06666 9999999
RELIC MANUAL CREDITS | 04222 4777777

 

[SkipVought]

Simple code is set up to import data into a number of worksheets

What is that code?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[FractalWalk]

If you are reading this in from a single text file and the headers always precede the data, then I would open the file in memory, loop through each record, adjust the data as needed and then print to a worksheet. For example:

Dim WriteToSheet(100, 2) As String, fle As String
Dim w As Integer

w = 0
Open fle For Input As #1
Do While Not EOF(1)
    Line Input #1, buffer
    If buffer = "MANUAL DEBITS" Then
        hdr = buffer
    ElseIf buffer = "MANUAL CREDITS" Then
        hdr = buffer
    Else
        WriteToSheet(w, 0) = buffer
        WriteToSheet(w, 1) = hdr
        w = w + 1
    End If
Loop
Range("A1:B" & w) = WriteToSheet

I didn't test that and it will only work if the text file format is exactly as you described, but this should give you an idea of what I am talking about.

 

[Vashkar]

First and above all, my knowledge of VBA is limited. But as seemingly the fast track can't be taken by lack of provided code I thought I would give it a go. I can't produce code by memory but it can easily be found in help.
This is how I would address this issue on my knowledge:

===
Determine how many rows are filled in active worksheet, columnA
set arrayvariable (2, number of filled rows)
Loop through filled rows
Place each row data in arrayvariable based upon
if found value = "manual debits" as from then array(1,#) "manual debits", Number found"
if found value = "manual credits" as from then arrayfill(2,#) "manual credits", Number found"
end loop

write array values where you want, adressing the array(1,#) for Debits, array(2,#) for credits.
====
Do keep in mind arrays devour memory, wouldn't advise it on huge amounts of data.
Perhaps a direct copy paste on a fixed point on a temporary made worksheet, shifting one row per paste, would accomodate that. After the now sorted data is taken from this temp worksheet and placed where it needs to go, just delete the temp worksheet.

 

[Randy11]

To resolve this.... Separated Manual Debits & Manual Credits to two separate worksheets. Then applied code like below to get desired results. Similar code with "RELIC MANUAL CREDITS" as the Value to place in Column A. Fast & simple.. Thanks for all the input.

Sub Manual_Dr_AcctType()
Dim c As Range

Sheets("Manual_Dr").Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A1").Select

For Each c In Range("B1:B1500")
If c.Value > 0 Then
c.Offset(0, -1).Value = "KINEAR MANUAL DEBITS"
Columns("A:A").EntireColumn.AutoFit
Range("A1:A2").ClearContents
End If

Next c
End Sub