Fitting data into fields out of an irregular list

Hi friends,

I have a list of data (names, employee IDs, work location, etc) in a single column in a tab that I would like to bring into a particular format. Each record is separated by a blank row. Essentially I'm trying to capture only 3 fields for each record- name, employee ID and location. However as in the following screenshot, I sometimes have less than 3 fields or sometimes more than 3 fields available for each record.

Picture1.jpg

I would like to write a macro whereby each record has exactly 3 fields even if they are blank. This would mean inserting any blank rows as needed to make each record have 3 fields; or deleting any extra fields after the 3rd row in a record.

I also want to bring this into a 3-columnar format until the list is finished (when there are no more records). I would appreciate any help with this. I have attached a sample worksheet.


The data exists in the 'Staging' tab, and the 3-columnar format exists in the 'Output' tab, where I would like to have the final output.

Thanks!

Hi vijaijohn,
just a quick question, how important is altering the staging tab to be a standard format? Will this be used or, as the name implies, simply be a working area with the actual Output tab being the deliverable?
Jmac1947

Hi Jmac1947,

Thanks for replying.

The Staging tab is just a working area. I can leave it blank after the clean data is moved to Output. I found a long-winded workaround involving 4 separate Macros, but I can't seem to loop the actions until the end of the list. It would help if the process could be automated.

Briefly, I did this:

Macro1 returned values through column B as to whether a corresponding cell in column A was blank or not. If it was blank the cell in column B would be "BLANK". This Macro also copied and pasted Column B with "values" only, so the returned value actually read "BLANK" instead of a formula.

Macro2 copied and pasted the first three rows in Column A in the Staging tab into the columns in Output. There are at least 2 non-empty rows, so in the worst case, the 3rd column in Output would be blank- which is okay.

Macro3 searched for the first instance from row 1 in Staging where it found the word "BLANK" and deleted all rows above it and including it. I wrote another Macro (Macro3b) to replace the first 2 rows which are header rows. Macro3 cleaned up after itself by calling this macro.

I assigned a short cut key to Macro3 and I keep applying this until the end of the list. As I said if I could automate this, it would be great.



VJ

Hi Jmac1947, Yes- there would be at least a name row and an employee ID row. So at least 2 rows in a record.

You're right about splitting the name. There is a long cleanup involving all fields after I get through this step

VJ

Hello vj,

If the attachment works then you can take a look at the macro in the attached spreadsheet.

It appears to work for excel 2010 at least.

You will see that I am an old fashioned programmer who takes it one easy step at a time (and am in complete awe of a number of people on this forum who do amazing things with very complex statements). I didnt do any real error handling, just concentrated on getting a simple solution.

Hope this helps your task

jmac1947

Hi JMAC, many thanks for this. The last name omits a letter at the end, which is the only issue. But it solves my problem of automating the process without having to apply the short cut key. Once again, thanks much!!

Hi vj,

alter one line only
ab = cell_length + 1 - aa
that is probably the easiest way to fix the issue
glad to help

Thanks! It works perfectly now!