merging data into consistant row headers with skipped fields

[johnahartman]

HELP! I have run a macro to bring text data into excel that is ordered
into two columns with a header called Field and one called Data. Each
set of form data varies i.e some people did not choose an option so the
field or data are not in the list so a simple copy/past/transpose does
not work. Each record is separated by the header.

Example:

A B
Field Data
field1 data1
field2 data2
field3 data3
Field Data
field1 data1
field2 data2
Field Data
Field Data
field1 data1
field2 data2
field3 data3

I am hoping someone has a handy macro that can either select data
between Field Data headers or one that could process through and do
loop at field headers and insert data into rows. For example

goto Field HEADER
if field = field1 then insert data1 into row A next column

This is for a non-profit and they need to get allot of member info
ASAP. I am putting into DB so they do not have to deal with these text
files. Any help would be greatly appreciated.

 

[Otto Moehrbach]

What you want done is easy enough to do except for the
inserting/moving/copying part. What do you mean by "...insert data1 into
row A next column"? There is no row A. Provide more detail about where you
want "data1" to go. Also, where do you want the next "data1" to go? Also,
what about the 2 and 3 fields and data? Do you want any of those
moved/copied somewhere? HTH Otto

 

[johnahartman]

I have a set of Fields that I want to be column names and I want to put
data or leave blank based on whether or not that field has data in it.

A B
Field Data
field1 data1
field2 data2
field3 data3
Field Data
field1 data1
field2 data2
** missing data3
Field Data
field1 data1
field2 data2
field3 data3
Field Data
field1 data1
field3 data3
** missing data2

So I need it to process everything between Field Data and the next
Field Data in the do loop( or whatever) and insert it into column A, B,
C etc. based upon original column B data. In the original XLS if the
data is blank it does not provide field it just goes to next.


Field Value
list on
email (e-mail address removed)
help on
interest Stuff
name John Doe
remarks More stuff
submit Submit Inquiry
url www.website.com
Field Value
list on
email (e-mail address removed)
help on
interest Stuff
name John Doe
submit Submit Inquiry
url www.website.com
Field Value
list on
email (e-mail address removed)
help on
interest Stuff
name John Doe
remarks More stuff
submit Submit Inquiry

so in this example 1st has all fields 2nd they did not leave any
remarks and 3rd they left no URL so I would still need to insert a
blank in spread sheet before I went down to the next row and started
with list for next set of data.

Example for final XLS:
A B C
1 field1 field2 field3 --- Column Names
2 data1 data2 data3
3 data1 data2
4 data1 data2 data3
4 data1 data3

Hope I cleared it up not muddied waters

Thanks.
Johnny

 

[kounoike]

I'm not sure this would be what you are looking for.
Asuuming data start at A1

Sub mytest()
Dim H1 As Range
Dim H1name As String
Dim clH1 As Long
Dim arfld
Dim clco As Long, i As Long
Dim rc As Long, rclast As Long
Dim cl

Set H1 = Cells(1, "a")
H1name = H1.Value

'Change fields name in Array()
arfld = Array("list", "email", "help", "interest", "name", "remarks",
"submit", "url")

clco = UBound(arfld) - LBound(arfld) + 1
Columns("a:" & Chr(Asc("a") + clco)).Insert
clH1 = H1.Column
rc = 1

For i = 0 To clco - 1
Cells(rc, i + 1).Value = arfld(LBound(arfld) + i)
Next

rclast = Cells(Cells.Rows.Count, clH1).End(xlUp).Row
i = 1

Do While (rc <= rclast)
If Cells(rc, clH1).Value = H1name Then
i = i + 1
Else
cl = Application.Match(Cells(rc, clH1).Value, arfld, 0)
If IsNumeric(cl) Then
Cells(i, cl).Value = Cells(rc, clH1 + 1).Value
End If
End If
rc = rc + 1
Loop

End Sub

keizi