Excel - Split data from summary worksheet

[pr24]

Could really need som help here from someone who's good at Excel!

I've got several hundred records of data in worksheet 1 that looks like this: company code, date, company name + som others.

FR 01.01.2005 Fire and Rescue
FR 02.01.2005 Fire and Rescue
FR 05.01.2005 Fire and Rescue
FR 04.01.2005 Fire and Rescue
HW 01.01.2005 Heat & Welding
HW 03.01.2005 Heat & Welding
HW 05.01.2005 Heat & Welding
HW 04.01.2005 Heat & Welding
SL 05.02.2005 Shipping Ltd
SL 08.02.2005 Shipping Ltd
SL 01.02.2005 Shipping Ltd


These records are to be separated to different worksheets. All records with Company Code FR goes to worksheet FR, Company Code HW goes to worksheet HW and so on.

I'm trying ta achieve this by putting formulas in each worksheet, in such a way that data that belongs in this worksheet are gathered automatically. I've tried LOOKUP, HLOOKUP etc, without any luck. The number of records for each company may differ from day to day.

As of today I'm using cut/paste, but was hoping to save me som work this way.

Any help are very much appreciated!!

 

[gizmo1973]

If all you data starts with 2 alphas try this

=left("your cell with data",2)
This will give you a list of FR, HW, SL etc

Then in your FR sheet type =IF("You Data Sheet" "The Cell That We Created Above" ="FR","your Data Sheet Original Cell,"")

This will look at your code (in the above case FR) and return the data if the code matches.
On the next sheet same equasion but with HW etc etc

Long and faffy and I'm sure someone else will have a better answer but this will work as it'as the way I do it.
If it's not clear let me know.

Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts

 

[xlbo]

Best baet is ADVANCED filter
Need a formula as per Gizmo above to seperate the company

You will need a named range on each sheet as the start point for the filter eg on the sheet you want Co. FR data, NAME range A1 "FR"

You will then need to go through the advanced filter setting the criteria range up with a criteria of FR and "New Location" of "FR" (ie the named range)

You would need a criteria range for each company

Bit longwinded to set up but should be very slick afterwards

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[pr24]

Thank you very much, both of you! Seems like the Advanced Filter might do the trick!!

Just needs a little more testing...

 

[xlbo]

It can be a bit "funny" about filtering to a new location if that location is on a different sheet - that's what the range names are for - they circumvent that problem - in code anyway

The other workaround (depending on your data) is to use a pivottable (again split the Co out). Put the Co as the PAGE field and then you have an option to create a pivottable for each Co on a new page for each Co automatically (at the click of a button - this is built in functionality)

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[gizmo1973]

^Agree with what XLBO said about Pivots ^


Regards, Phil.

M.U.F.C. Show your true support here:

http://shareholdersunited.org/join.php?ref=125059000

"Shares not Shirts