Organizing excel information into multiple lines

**bryster126** · 04-25-2012, 09:54 PM

Here is a file explaining what I want to do: ex1.xlsx

I have an excel spreadsheet that is tracking attendence on different dates. The spreadsheet goes like this

1/1/12 2/2/12/ 3/3/12 etc
----------------------------------------------------------------------
Joe Smith Jane smith John smith
Joe doe Joe Smith Jane smith

there are many more names and many more dates. What I am trying to achieve is this: I want to be able to format the spreadsheet such that an individual name stays in the column that it is in, but it moves to a row that has all of the same name in it. Like so:

1/1/12 2/2/12/ 3/3/12 etc
----------------------------------------------------------------------
Joe Smith Joe Smith
Jane smith Jane smith
John smith
Joe Doe

Some spots will be empty since they may have not been there that day. Is there any way to do this in excel?

EDIT: The formatting is messed up. Hit "reply with quote" to see the correct formatting.

**bryster126** · 04-26-2012, 05:29 PM

anyone have answers?

**bryster126** · 04-26-2012, 08:13 PM

I really need help kind of quick. If anyone has an answer that would be appreciated.

**bryster126** · 04-26-2012, 09:05 PM

gah I guess no one can answer

**Herman Merman** · 04-26-2012, 09:27 PM

I think the only way to do this would be using VBA code, is this OK for you?

**bryster126** · 04-26-2012, 09:31 PM

VBA code is completely fine as long as it solves my problem. This would be relatively simple to do with coding, but I just don't know how to code for excel. How would I implement this VBA code?

**bryster126** · 04-26-2012, 09:39 PM

is anyone who knows VBA able to do this for me? I have no experience at all with VBA

**Herman Merman** · 04-26-2012, 09:45 PM

The macro would have to assign each person to a row and re-organise the data according to that assignment. I assume you're using a new column for each week, but is this continuous? (I'm guessing you want a whole year in one worksheet).

The other way to go would be to summarise the data for each person based on the raw data entered in the worksheet - that wouldn't involve using VB and would be a lot more straight forward. This would essentially be a table with each persons name down the left hand side with the column headers each being a date, a COUNTIF formula could then lookup the number of times each person is present for that date. Would this meet your needs or do you have to show continuous columns of each persons name?

**bryster126** · 04-26-2012, 09:57 PM

Having a final number number with the amount of times they were present is fine as well. something like this would also be fine:
-------------------------------date1------------date2--------
person's name----------------- here ------------ here----
other name ----------------------here------------here

There are also some names that may be typed incorrectly. Such as joe smith may be joe smilth. As long as both the names are written on the left hand side, I could edit this out.

To summarise: All I need to know for now is how many instances of there name occurred along with a list of the names.

Also, if by continuous you mean I'm going to add more data to it, I'm most likely not.

**Herman Merman** · 04-26-2012, 10:04 PM

Simple VLOOKUP in the end solved it - File attached.

**bryster126** · 04-26-2012, 10:06 PM

How would I do this in my original spreadsheet?

**Herman Merman** · 04-26-2012, 10:20 PM

OK, you need to create a seperate "Report" worksheet which reads the information from the "Data" worksheet. The Data worksheet holds all the raw data while the report worksheet categorises it by name and date. The VLOOKUP formula then looks in the data worksheet for a match to the persons name on the left and if it finds one returns the same value, if it doesn't find a match it returns a blank value. If you like you can send me your original spreadsheet (ivortollah@gmail.com) and I will do it for you.