Two problems
Posted by Matteo on September 20, 2001 10:06 AM
1. Most important is a way to get all the data on one row per Emp#. There are 8441 rows so it's not feasable to do it manually.
2. Emp# to actually be five digits (it shows that way now but when I bring it into Access it comes in without the zeroes preceding). Attached is a sample. Thank you..
Emp #
00042 1,400 1.89 0 0.00%
00042 0.00%
00050 800 1.81 1,500 3.33%
00050 500 1.14%
00079 2,000 3.89%
00112 2,500 3.03 2,500 2.94%
00112 2,400 3.00%
00117 6,700 9.13 7,300 9.11%
00117 5,400 7.94%
00119 2,500 5.21 2,800 5.54%
00119 2,000 4.35%
Posted by Barrie Davidson on September 20, 2001 10:39 AM
Matteo, here's what I'd do.
1. In column F (I'm assuming you've got a maximum of 5 colums of data) I would put the following formula:
=IF(ISBLANK(E1),1,0)
I would then sort on column F. Once you've got your listing of data with 5 data points (the ones with a value of 1 in column F) use a VLOOKUP function to return the data from the data table with only three data points (00050 500 1.14% for example). Put the VLOOKUP formula in column F,G and H, over-writing the IF formula in column F for those rows with a value of 1. Copy and PASTE VALUES the information in columns F, G, and H. You now have one record (row) of information for each employee.
2. Select your entire column of data and, from the main menu, select Data|Text to Columns and follow the prompts. When you get to step 3, select TEXT as your column format. This will convert the numbers to text. Also, when importing in to Access, make sure you specify a text data type for your first column of data (numbers don't have leading zeros).
Hope this helps you out.
Regards,
BarrieBarrie Davidson
Posted by Matteo on September 21, 2001 3:39 AM
Barrie, thanks for the response but I'm a little confused about the VLookup formula, how do I write it, etc. Also, I wasn't clear in my initial example. There is different data in different collumns and no data in different collumns all the way through. Will the IF statement still work ? Here is a clearer example:
03634 1,800 2.42 3,000 3.94%
03634 1,200 1.81%
05717 1,000 1.47 3,200 4.65%
05717 0.00%
And there are some rows with just one Emp# (1st column), hence one row of data but not in each of the six columns of data.
Thanks...
Posted by Barrie Davidson on September 21, 2001 5:37 AM
03634 1,800 2.42 3,000 3.94%
Matteo, it appears from your example that you have two records (rows) for each employee number. The first record has five data points (your example is: 03634 1,800 2.42 3,000 3.94%
) and the second record will have 1 to 3 data points (employee number being the first data point). Is that correct?
Barrie
Barrie Davidson
Posted by Matteo on September 21, 2001 8:43 AM
Barrie, In 95% of the data, the first row has 1 data point (empl#) followed by two blanks, and ending with four data points. Total of seven data points.
The second row (same empl#) is the inverse- first three data points followed by four blanks.
Thanks... :
Barrie, thanks for the response but I'm a little confused about the VLookup formula, how do I write it, etc. Also, I wasn't clear in my initial example. There is different data in different collumns and no data in different collumns all the way through. Will the IF statement still work ? Here is a clearer example
Posted by Barrie Davidson on September 21, 2001 9:34 AM
The other 5%
Matteo, what about the other 5%? And, just to make sure I understand, the first row has data from columns A to G with column B and C being blank. The second row has data from columns A to G with columns E to G being blank. Is this correct?
Barrie
Barrie, In 95% of the data, the first row has 1 data point (empl#) followed by two blanks, and ending with four data points. Total of seven data points. The second row (same empl#) is the inverse- first three data points followed by four blanks. Thanks...
