Help with organizing a messy table

l KAUTION l

Board Regular
Joined
Nov 3, 2005
Messages
90
I have something like:
(F1, F2, ..., F7 are column names)

F1.......F2.......F3................F4.........F5..........F6................F7
Pol#...04...000003261.....John.....Wood.....S100568
.......................................2007.....Harley......................1HD5DG5D696
...................................................................................Error: Blah
Pol#...06...000005553.....James...Brown....F516D52
.......................................1996.....Plym........................55ADG6546D98
...................................................................................Error: Blah Blah
.......................................1968.....Chev........................55D6D4F1RE77
...................................................................................Error: Blah

I would like to get all of that on one line in case there is a certain criteria that I need or don't need. Such as:
F1......F2.......F3..............F4........F5...........F6............F7........F8.............F9..........................F10..............F11....F12.............F13..................F14
Pol#...04...000003261...John...Wood...S100568...2007...Harley...1HD5DG5D696
Pol#...06...000005553...James...Brown...F516D52...1996...Plym...55ADG6546D98...Error: Blah Blah...1968...Chev...55D6D4F1RE77...Error: Blah

Maybe there is a way to in column F8 if column F1 is Pol#- the next Pol# then F8 = 1, then 2 for the next set.

Hopefully I have explained myself somewhat clearly. Thanks for your help in advance mrexcel members!
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
how many rows of data do you have? it might be easier to do this in excel.
 

l KAUTION l

Board Regular
Joined
Nov 3, 2005
Messages
90
Is there anyway to do this in access. This file is quite large and I want to avoid the manual tasks every month. Thanks for your reply however.
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
I wouldn't advise doing it in access because the end result you are looking for is not normalized. Sorry I couldn't be more help.

Giacomo
 

l KAUTION l

Board Regular
Joined
Nov 3, 2005
Messages
90

ADVERTISEMENT

Is there a way to make column or field 21 say something like:

..F1.......F21
Pol#:.......1
null..........1
null..........1
Pol#:.......2
null..........2
Pol#:.......3
null..........3
null..........3

Basically Increment the counter in F21 if F1 = "Pol#:"
 

NumbersMax

Board Regular
Joined
Jun 19, 2007
Messages
200
Could this problem be tackled at the source? What I mean is, I've run into problems where the input I'm trying to work with is impossible, but when I've worked with the people putting in the source data, together we've come to a compromise that benefits both parties. Usually, they are more open to change when a change lightens their data entry or redundant task, so I try to think of ways I can automate their process and still get what I need.

That being said, it looks like your data might be imported from a spreadsheet, database or something else rather than direct entry into the db you are using. Is this the case?

Can you create a form they could input the info directly into your db?
Can you pull the information differently?
Could they be convinced to copy down the POL# across all the entries or put all the entries on the same line somehow?

I've been trying to think of a way to get it all on one line, but it has escaped me so far...

I would suggest you do a search on Normalizing your data just to make sure you are changing the data into a format that will best serve you down the road. Check this link:

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Basically an example would be that you wouldn't want the 2 car models on one line and probably not in the same table with the pol# (policy number) anyway.

Maybe something like this:

PolicyTable:
Policy #
Customer #
Veh ID #

CustomerTable:
Customer #
Customer Name
Address
etc

VehicleTable:
Veh ID #
Make
Model
Year
etc

In the Tools/Relationships menu option, you could link the tables by their common fields Veh ID # or Customer #. This would allow you to make all sorts of cool queries that give you the answers you want instead of constant headaches with duplicate results.

Please answer the questions about the source of your data...maybe someone can give better advice on how to pull it so you don't have the messy table from the start!

Max
 

l KAUTION l

Board Regular
Joined
Nov 3, 2005
Messages
90

ADVERTISEMENT

I am guessing that the data comes from another database and is exported as a text file. In some cases it is exported as a rpt file. Which I need to manually convert to a txt file before I can import that, unless there is a way to import rpt file.

I'm not sure I can contact the source of the data. I am thinking that the data comes from an outside the company source.

I am trying to figure out a way to normalize this database. There aren't any common "keys" between any of the fileds. That's why I'm trying to figure out if there is a way to put a 1 in all of the rows of the first record, then a 2 for the second records different rows and so on. I am going to try to find out where exactly we get this data, but I have a strong feeling that it is the way it is.

I hope that answers your questions. Thanks for your help thus far!
 

l KAUTION l

Board Regular
Joined
Nov 3, 2005
Messages
90
I just found out that the data comes from a third party vendor. The vendor has been asked to change things before and was unable to. I just need to get a common field in there somehow. Is there a way to copy the last field 2 the was not null to the field 2's that are null until there is a new field 2 and the copy that field 2 and so on?

There has to be a way. I am going to keep messing around with this until I can figure it out or someone has some sort of solution. I am not very good at vba but maybe that is the way that I need to go...?
 

NumbersMax

Board Regular
Joined
Jun 19, 2007
Messages
200
I would continue to pursue the vendor for a solution. Of course, you can still try to work it out in the meantime. It is good that it's a vendor, because at least your company may have some ability to set requirements with them. If it was a customer, you'd probably be completely out of luck.

It may be worth the time to investigate if they could simply run the query behind the report (assuming that is the way it's setup) and send you a spreadsheet of the query results or maybe a delimited text file of some sort. They could send it along with the reports they currently send or instead depanding on the needs of others in your organization. You don't want the report because it gives you exactly what you have...discorganization.

A few years back, my Materials Manager required our primary freight carriers (Con-Way and FedEx Freight) to provide us with spreadsheets of our shipments via e-mail. We have integrated that into our current system by having one of our better Excel people create macros to manipulate the data in Excel to line it up right for import into Access, where I created macros to handle the consolidation, saving and e-mailing of the data to different people in the organization.

It can definitely be done if someone at your organization is willing to lean on them just a little :cool:
 

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
I think NumbersMax has given you some pretty good advice so far. However, if you are stuck with the data as is you could normalize it with a bunch of clean-up queries. The first thing you need to do is to add an auto-number field to your source table. The auto-number will be crucial in later steps for keeping everything in order, and for relating back to the source.

The query below will populate F2 with the pol# from above where it's null in the source. After you've done this it should be pretty easy to split up this table into a normalized table structure.

Going into this you should have your existing fields F1 - F7 plus an auto-number field named ID. I will call your existing table tblSource in the example, I am assuming it's laid out like this:

<TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0><FONT FACE="Calibri" COLOR=#000000><CAPTION>tblSource</CAPTION></FONT><THEAD><TR><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>ID</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>F11</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>F2</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>F3</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>F4</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>F5</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>F6</FONT></TH><TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>F7</FONT></TH></TR></THEAD><TBODY><TR VALIGN=TOP><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>9</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>Pol#</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>4</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>3261</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>Wood</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>S100568</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>10</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>2007</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>Harley</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>1HD5DG5D696</FONT></TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>11</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>Error: Blah</FONT></TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>12</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>Pol#</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>6</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>5553</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>Brown</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>F516D52</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>13</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>1996</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>Plym</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>55ADG6546D98</FONT></TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>14</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>Error: Blah Blah</FONT></TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>15</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>1968</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>Chev</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>55D6D4F1RE77</FONT></TD></TR><TR VALIGN=TOP><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>16</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ALIGN=RIGHT><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>
</FONT></TD><TD BORDERCOLOR=#d0d7e5 ><FONT style=FONT-SIZE:11pt FACE="Calibri" COLOR=#000000>Error: Blah</FONT></TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Update SQL:
Code:
UPDATE tblSource 
SET F2 = DMax("F2","tblSource","[ID] <= " & [ID])
WHERE F2 Is Null;

hth,
Giacomo
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,569
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top