select statement moving rows to columns (1 Viewer)

lwarren1968 · Dec 12, 2019

I received an excel file where part of the record set (UPC#) is on the second line. I need to some how build an select statement incorporating it into the 1st line of each record if that makes any sense! I've attached the file I am referring to for referencing/viewing.

Gasman · Dec 12, 2019

What a mess :banghead:
Column T is also repeated but on it's own for a whole record.

I think you might have to process this as a recordset and vba?

The pattern does look consistent?
One line of data
One line of data in field 3
Ignore the next line

HTH

lwarren1968 · Dec 12, 2019

Sorry I know. I was asked to combined several files, which was easily done via a power query, but when I reviewed the detail I thought the same!

FYI, column T is not needed in my end result. Its just the file name. I only need the UPC to be carried over. Line 3 is not needed either. I'm not an expert (self taught), so if there is a way one can help I'd appreciate it!

Gasman · Dec 12, 2019

If you can go back and format it correctly, that is what I would do.
If you cannot change it then...

something along the lines of

Code:

Private Sub Command0_Click()
Dim strSQL As String
Dim rsIn As Recordset, rsOut As Recordset


strSQL = "Select * from <YourLinkedTableName>"

Set rsIn = CurrentDb.OpenRecordset(strSQL)
Set rsout = CurrentDb.OpenRecordset(<YourTableName>)
rsIn.MoveFirst

Do While Not rsIn.EOF
    ' Set the first fields
    rsOut.AddNew
    rsOut!Field(0) = rsIn!Field(0) 'Or as you require the fields, I am assuming same order?
    rsOut!Field(1) = rsIn!Field(1)
    ' ...... etc
    ' Now read next record for UPC
    rsIn.MoveNext
    rsOut!Field(n) = rsIn!Field(2)
    rsOut.Update
    ' Read third record in set and ignore
    rsIn.MoveNext
    ' Is this passed the very last record ?
    If Not rsIn.EOF then
        rsIn.MoveNext
    End If
Loop

Set rsIn = Nothing
Set rsOut = Nothing

End Sub

Note, no error checking.
Link to the workbook for your table name.
Your last record does not have the third record hence the If..EOF

HTH

isladogs · Dec 12, 2019

Indeed a mess but fixable in Excel (see attached)
This is what I did on a copy of your file - try it yourself and hopefully you'll get the same result as me

1. Select whole sheet and Unmerged cells
2. Added 2 new columns D & E after C & D
3. In new cell D1 I entered = C1 & " " & C2
4. Fill down column D
5. Now highlight column D, copy & paste VALUES into column E
6. Delete columns C & D as no longer needed
7. Filter column A and select Blanks. Delete all those rows
8. Remove filter & if you want remove any unwanted columns

NOTE: you can probably just do steps 3,4,5 in two steps but I like to be sure to avoid errors

select statement moving rows to columns (1 Viewer)

lwarren1968

Registered User.

Attachments

Gasman

Enthusiastic Amateur

lwarren1968

Registered User.

Gasman

Enthusiastic Amateur

isladogs

MVP / VIP

Attachments

Similar threads

Users who are viewing this thread