Organizing data from a column into appropriate columns

[Rain95_c]

Hi!

I have an excel file generated from a third-party software. The problem is that the data in a column is not organized according to category. I wonder if there is a way I can organize the system generated files to suit my existing format.

The file I have attached has three sections. Column A and B are the data (and format) I get from the software. The columns and rows highlighted in yellow is the format I need the data to look like. The columns and rows in light red is what I have so far.

The problem I am having is that I really don't know how to use the array functions yet so I don't know which part of the formula to change or how to point to a reference. The results of my efforts have only been copy-pasted formulas from similar posts. Also, since there are instances where a single incident requires multiple entries (because there are multiple different victims or multiple offenders), the formulas I have do not really work. The third problem I have run across (still because I do not understand array functions yet) is that some data will be listed based on the adjacent column, while some will be listed based on the value of the cell two rows beneath it.

Please help.

Thanks in advance.

rain.xlsx

[JBeaucaire]

Here's an ugly but easily manageable way to do it. The formulas in the GREY section are collecting the data into row format and then M column is flagging which rows are the ones to "pull out" over on the OUTPUT sheet.

To update the Raw Data, highlight columns A:B and press DELETE to clear the cells gently. The do a simple copy/paste to drop the new data into columns A:B.

The formulas in the grey section are currently copied down 300 rows, so that should cover a lot of data, but you can easily copy those cells down further if the data ever extends below row 300.

On the OUTPUT sheet it is collecting the INDEX rows from the column M of Raw Data. It is currently set to show 40 rows of data, but you can copy the yellow down further if needed. I've added a message in K1 to urn red if you need to add more.

[Rain95_c]

Im going to try this out in the office. It looks like it will work great!

I'll update you as soon as i get the chance to go online again. For now, I should already thank you a lot JBeaucaire.

I would appreciate a "array formulas for dummies" type of explanation on the formulas too, if it isnt too much to ask for. I would not want to depend on you forever , and more importantly, so i can pay forward.

rain

[FDibbins]

Rain, I can see no ARRAY formulas in Jerry's file They would be wrapped in {}

If you point to a specific formula, perhaps I can help

[JBeaucaire]

Ford correct, they are all simple IF/THEN constructs to pull values from column B then either copy then down as needed, or in the case of the TEACHER column, copy them UP as needed.

The M column is looking for instances of the word "Victim" because those are the rows that need to be pulled eventually, one output row per victim.

The OUTPUT sheet is mostly INDEX/MATCH formulas.

You can select any formula you're interested and decipher it by using the EVALUATE FORMULA function. I have no idea where that resides exactly in Excel 97.

[Rain95_c]

Sorry if it took so long to reply. The computer at home needed some repairs.

I have tried the formulas out in the office and have encountered two problems.

1.) The collection of the date does not seem to work. It returns an #N/A value. I have tried changing the date formats but to no avail.
2.) Some reports fail to write down the Student as an offender. So instead of "Student, Offender" it is simply written down as "Student". I'm sure this should just be a minor tweak to the original formula. I tried adding the asterisk after (Student*) and ended up laughing at myself.

As for the array functions, I'm sorry. My bad. My initial efforts were sorry attempts at arrays so without trying the formulas first, I presumed that arrays were used.

Thanks you so much for the replies. Again my apologies for the late updates.

[JBeaucaire]

Can you show me the problem in a workbook? Thanks.

[Rain95_c]

I am so sorry for taking forever to reply to this thread. It's a very long story. Thank you so much for all those who tried to help.