Excel Data Masters! Help!!

Factual Questions
1

You guys have come through for me before…

So I have the data in excel as complete records, almost 2500 of them, and each record is composed of 33 fields.

It now is assembled thusly:

[ol]
[li]RECORD one [/li][li]name joe[/li][li]address usa[/li][li]phone 758[/li][li]favorite food burgers[/li][li]hair color brown[/li][li]computer PC[/li][/ol]
[ol]
[li]RECORD TWO [/li][li]name bill[/li][li]address japan[/li][li]phone 9273[/li][li]favorite food rice[/li][li]hair color green[/li][li]computer MAC[/li][/ol]

[ol]
[li]RECORD THREE [/li][li]name joe[/li][li]address Brazil[/li][li]phone 99999[/li][li]favorite food beans[/li][li]hair color blonde[/li][li]computer PC[/li][/ol]

[ol]
[li]RECORD FOUR [/li][li]name Mary[/li][li]address usa[/li][li]phone 8273[/li][li]favorite food burgers[/li][li]hair color blonde[/li][li]computer Mac[/li][/ol]

And I want it to be this:


**Record Num    name	address	phone	food	hair	computer**
Record One	joe	usa	758	burgers	brown	PC
record two	bill	japan	9273	rice	green	MAC
record three	joe	Brazil	99999	beans	blonde	PC
record four	Mary	usa	8273	burgers	blonde	Mac

This is such standard stuff I know there must be a way. I just can’t figure it out.

Help!

2

you want to use Table view

3

I don’t seem to have that option. I have tables, but not much information as to how to make that work. I tried pivotable, tables, filters…

4

do the cells actually have the number in them as in: 1. Record One

5

so you have 2500 data records that each take up 33 lines… all in column A? Is there a one blank cell space between each record?

6

Yep, there are a dozens of ways. I’d typically write a macro for this but I tried to see if there’s a way to not write any VBA and one idea was using TRANSPOSE() function with array formulas.

Using your example of 7-lines-per-record, I pasted it into Sheet1.

In Sheet2, I create a “helper” column I which simply has the row #. Cell I1 would be 1, I2 would be 2… drag that down until you get 2500. This values column “I” will be used in the TRANSPOSE(OFFSET()) function.

In cells A1 to G1, (7 columns from “A” to “G” because you have 7 rows per record), I enter an array formula (a CSE Ctrl+Shift+Enter type of formula) which is:


{=TRANSPOSE(OFFSET(Sheet1!$A$1,(I1-1)*7,0,7,1))}

I then make sure A1 to G1 is selected by the cursor and drag it down, and each row will show the “transposition” of the 7 rows of Sheet1 to the 7 columns of Sheet2.

If the end result of Sheet2 being array formulas restricts you in some way, you can paste the entire 2500 rows to Sheet3 using “Paste as Values”.

For your real-world data, you’d replace the “7” in the array formula above with 33 (or 34 if there’s a 33 actual rows of data + 1 blank line in between each group of rows). You also need to enter the array formula in columns A1 - AG1 (because AG1 represents the 33rd column address). Also, the “helper” would not be in column “I”, it would shift to column “AI”.

The instructions above look more complicated than it actually is. It should take about 5 minutes max.

There’s probably a more elegant way to do this without VBA but TRANSPOSE(OFFSET()) technique is what came to mind first.

7

I would look for blanK cells between the groupings and then combine all the data into one cell. You would need a blank line above the first group and only 1 line between the groupings.

formula in cell B2 assuming row 1 was was blank and cell a2 was the first grouping of information - ex: =IF(ISBLANK(A1),A2&""&A3&""&A4&""&A5&""&A6&""&A7&""&A8,"")

This will display: 1. RECORD one2. name joe3. address usa4. phone 7585. favorite food burgers6. hair color brown7. computer PC

From there you can create columns using text to columns and use the * as the symbol to use for spacing.

You can then highlight everything and use the replace tool (Ctrl H) to eliminate the words you don’t want. (ex: replace the word “record” with “”)

You could do all of this with individual formulas but you would have to look for the blank space and return the information in the cell next to the word you don’t displayed using the MID function. That would look something like this in cell b2: =IF(ISBLANK(A1),MID(A2,8,50),"") The mid function counts over to the space you want and returns x number of charters specified which in this case would mean it counted over 8 characters from the left and returned the next 50. The formula in the next column would look for the same blank cell and reference the information in the next cell down.

8

I have to do this kind of thing all the time and here’s how I tend to solve it (although there are undoubtedly much more elegant ways).

Let’s use your first record as an example, say that data is in cells A1-A7.

I create a formula in cells B1-H1 that looks for the word “Record” in A1 and then just copies the data A1-A7 into B1-H1 (there’s your transpose) and also strips out the field names (so you get “One” in B1 as opposed to “Record One”).

The formula in B1 would be this:

=IF(LEFT($A1,6)=“Record”, RIGHT(A1,LEN(A1)-7),"")

Parsing this out, B1 checks the first 6 characters of A1. If it’s “Record” then the formula will copy the contents of A1 minus the word ‘Record’ and the space into cell B1. If A1 didn’t start with “Record” then you get a blank.

Repeat for C1, D1 and so on. All you need to modify in those cell formulas is what value gets copied. For example in C1 the if-true part of the formula would be:

…RIGHT(A2,LEN(A2)-5)

because A2 starts with “name_” you strip out the first 5 characters

In D1 it’d be:

…RIGHT(A3,LEN(A3)-8)

because A3 starts with “address_” you strip out the first 8 characters

And so forth. The first part of the formula (LEFT($A1,6)=“Record”) doesn’t change. It may look like a lot of typing but it literally took me about 2 minutes to do by hand.

So you do that for your very first row, then just copy all those columns down. You’ll get one row of transposed fields at every row starting with “Record” and all the other rows will be blanks. You can highlight everything and do a Data-Autofilter then set the filter on “Non-blanks” for column B and that will compress it down for you.

You’ve got 33 fields so more columns to do but it’s really simple.

9

I haven’t yet puzzled over these solutions carefully (this is the kind of thing I have to read very slowly and connect each dot in order to understand it…just reading it through make my brain hurt) but I realize I wasn’t entirely clear.

The way the data is currently laid out is (ignore …it’s just for spacing):

…Column A… Column B
Row 1… Name…Joe
Row 2… Address…USA
Row 3…etc etc etc to row 33, which is the end of the record, then:
Row 34…Weird characters only in first cell
Row 35…===================
Row 37…Name…Bill
Row 39…Address…Japan
Row 40…etc etc etc to:
Row 70 Weird characters only in first cell
Row 71 ===================
Row 72 Name…ETC ETC

So it’s actually two columns. (Well, three, really… it’s column B&C, column A is where the weird character is and the beginning of the =======, but that can be easily taken out. Unless it helps…)

10

If the word “Name” is consistently used then you could put a formula in C3 as follows:
=IF($A2=“Name”,$B2,"") and copy it to the right. You then have to change the last cell reference to b3, then b4 and so on. Once you’ve got all 33 formulas just copy them down. This works only if the reference “Name” is an exact match with no other information in the cell.

If not then you need to reference the “=” sign or other unique key as follows in column C preferably near the top of each group of data :
=IF(ISNUMBER(SEARCH("=",$A1)),$B2,"") This will look in A1 for an equals sign. If it sees any at all it will return whatever is in cell B2. You would then copy the formula to the right and change the last cell reference to B3 and then B4 and so until you get to the cell containing the last bit of data in the group. Then you can copy it all down. Using the Search function will allow you to look for individual characters or words that are unique. It doesn’t matter if what you are looking for is upper or lower case.

What is important is that you have a consistant key to reference at the begining of each group. It can be the “=” sign or the word “Name” but it has to be unique to the begining of each data set.