Multiple Rows into single row on Different worksheet?

first.. I am a novice excel user and first time poster!.

i selected general section as i have no idea if this is possible. I have one workbook, and data on different sheets.

Here are examples of my sheets.

first one is called Factory. (these records are by item code, so all items listed no dup's) column heading in () data seperated by ,

(Item Code)(Description)( Qty) (VOL) (Weight) ( Factory) (3MoSales) (po no) (ETA) (QTYon PO)
ABC , test item , 412 , 0.00 , 15 , MADISON , 899 , XXPO NoXX , XXETAXX , XXQTY on POXX
DEF , test item2, 567 , 0.12 , 17 , BOSTON, 950,

next sheet is called OpenPOs

(Item Code)(PO No) (ETA ) (Qty On PO)
ABC , 0006627, 06/14/2015, 380
ABC , 0006688, 05/30/2015, 290
DEF , 0006627, 06/14/2015, 420
DEF , 0006688, 05/30/2015, 480
DEF , 0006627, 06/14/2015, 325
XYZ , 0006688, 05/30/2015, 380



is it possible to read the openPOs sheet, and match the item code, and then have it insert the PO no, ETA and Qty on PO running horizontal(same line) as item on the Factory Sheet?
the OPen PO's can have one or many open PO's listed for each item, but it is sorted by item.

any help or thoughts would be welcome.. remember i am a novice trying for this complicated situation.


Peter

this is a simple vlookup or index/match formula. A sample workbook will help. But here's an example of the formula you need.


=INDEX(OpenPos!$B:$B,MATCH($A2,OpenPos!$A:$A,0))

Welcome to ExcelForum Peter! Nice to see a fellow Chi suburbs resident on here!

Igoodable's approach will not work because the PO numbers (at least per your example) are not unique numbers. The problem with VLOOKUP and MATCH/INDEX formulas is they return the first line based on the number. Could you attach an example of your workbook so we can take a further look?

Best luck!

Thank you..
i have attached a partial workbook.
it has three sheets on it.
the main sheet is FactoryCBM. that is where i have the list of items. and the multiple POs is on the OPenPOs tab.
i need the PO No,ETA,Qty On PO columns from the OpenPOs tab to be on the same row (itemcode) of the FactoryCBM tab.

so, if there are 2 open po's, i need them horizontal repeating all 3 columns for each openPO. so then the first line it would add 6 columns

hopefully i have given you enough information.

Peter

I understand the goal, but I do not understand the purpose. Could you provide a little additional detail in regards to what you are trying to track/measure/accomplish? There is no "quick and easy" way to do what you are looking to do. Maybe there is another approach or solution I can help you find.

i create the 3 sheets in my sample excel, with crystal reports out of MAS100(ERP), i was unable to join all the tables in one report so i needed to run three reports which i export into excel.
The goal is to have one sheet/row for each item, we use this information to build a purchase order, we are a furniture importer so we need to build the PO with our eye on weight and Vol(CBM) to fit in a container, so part of that whole thing is we need to know if there are open PO's so we can either increase or decrease our "Build" depending on whats in the works.
right now we create a spreadsheet all manually as this information is not easily available and it takes along time. my spreadsheets have sped up alot, but if there was a way to get this in one sheet, it would be easy to view and would really help out our purchasing dept.

So is purchasing doing calculations off of this data? Like Qty On PO * Weight = x and then if x > Container capacity, increase order based on Available Qty or something like that?

For example... Item R2-140, PO 0006627 has a qty of 380. Times the weight of 15 (lbs?), that gives us 5,700 (lbs?). But, a container can hold 6,500 (lbs?) so lets add 53 additional units! (I made up container capacity - no clue what a real value would be.)

I got 53 by taking the container max - current weight and divided that difference by the weight per item (6500 - 5700)/15 = 53.333 rounded down to 53.

yes, they are doing calculations off of the results from spreadsheet, i have my items not only sorted by item, but by factory, so they can build a container for each factory which has specific items. ,but a big part is knowing if there are open PO's.

I think you may be looking at the data upside down, for lack of a better term. I would try using the Open POs tab as the primary page, pulling the rest of the information into that. I would do this because, based on what you described to me, identifying the Open POs is the primary objective.

It will appear that certain things are "duplicating" (like description for example) but they are not because the "Item Code", "PO No", and "ETA" create a unique line. In database management, we call this a Key.

Working the data like this, you can quickly create calculations while knowing that everything you are working with is based on Open POs. If you look at "Sheet1" in the attached, it is a simple pivot table that kinda breaks things down a little bit. Organizing your data in this manner allows for this.

Take a look and let me know if that works for you! Hope I helped.

Hi Newdoverman! PeterCCI, it appears newdoverman and I are on the same page. Hopefully between the two of us, that all makes sense.

oh my, that is a great idea. your right i got stuck looking at the data one way..

can you make that pivot table be like New doverman's bottom example where it seperates by item..

so appreciate all the responses

second.JPG

PeterCCI,

If you want reports how newdoverman has it, the pivot table cannot provide this without adding to the "raw data" where raw data is your Open PO tab. If you want, for example, to include a month into the report do the follow to the raw data.

Right-click on column A and select insert. In A1, enter "Month". In A2, enter the Formula =MONTH(D2). That will return a value of 1 - 12. If you would rather see month names, enter this formula. =CHOOSE(MONTH($D2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Using this method, and other formulas like the above (for example =WEEKNUM and =YEAR) you can add additional data that can easily let you filter the data based on what you want to see.

Otherwise, your company needs an Excel Consultant, I am always looking for work! I am based out of Aurora, IL after all

Thanks Newdoverman, i will try and get that into my full spreadsheet and see if i can link up a few more items to it.

but i like where its headed..

eric. Sounds good, do you do training classes? I really dont use excel that much or often, this is a one time request that I received.
but the girls in the office didnt know how to help me, and we just recently went to 2013 pro.

I've only done training classes for my current company, where I am a Business Analyst. While training classes are not something I am really looking to do, I am looking for additional contract work. Doing things like giant projects using Excel/Access to create and automate custom tools and solutions for your business.

If you are comfortable, e-mail me at eric.bartha@syncreon.com and I can send you an example of an Executive Dashboard I am currently working on.

I'd like to add, the only reason I am not open to training classes at the moment, is I do not want to have to use a PTO day to hold such a class. If we could work out a reasonable price that meets both of our needs though, I would of course reconsider. Also, if we could work out an evening or weekend class, that would work too.

Best wishes on your future endevors and again, welcome to Excel Forum!