Transfer Cell Data From 1 Sheet To Another Based on Criteria

I am not sure whether to use a table or formula to do this. The attached has 4 sheets. The 1st one has the the final data I want to get from the 3rd and 4th sheets and the 2nd sheet is where I want to pull the information to. I am looking for DCT MISS, LON & KIT in column E and when it finds one of them, it should transfer data from the same row in columns A, D & F to the manifest in the appropriate location.

It would be easier if sheets 3 and 4 were consolidated on 1 sheet but the powers that be want to retain the same layout although I could do a macro for that. Any and all help appreciated.

Thanks

Yes, it is only the 3 starting with DCT that are relevant. I understand your concern about the 10 spaces and while it is unlikely that they will be exceeded, I hope when I see and understand how you have done it, I will be able to make any future adjustments myself. Looking forward to your response. Thanks

In the attached file I've set this up for you using a few formulae, as follows:

I've used column H as a helper column in both the Pickups and Other sheet, with this formula in H5 of both sheets, copied down beyond your data (as can be seen by the hyphens):

=IF(E5="DCT KIT","Kit_"&COUNTIF(H$4:H4,"Kit_*")+1+H$1,IF(E5="DCT LON","Lon_"&COUNTIF(H$4:H4,"Lon_*")+1+H$2,IF(E5="DCT MISS","Mis_"&COUNTIF(H$4:H4,"Mis_*")+1+H$3,"-")))

In addition, I've used cells H1 to H3 to pick up the count of the KIT, LON, and MIS entries from the previous sheet - in the Pickups sheet these are all set to zero, but H1 in the Other sheet uses this formula:

=COUNTIF(Pickups!E:E,"DCT KIT")

with similar formulae in H2 and H3 for the other types. The formula maintains 3 unique record codes, by adding a sequential number onto each of Kit_, Lon_ or Mis_ as appropriate. These numbers continue onto the Other sheet.

On the Manifest sheet I've used this formula in A5:

=IFERROR(INDEX(Pickups!A:A,MATCH("LON_"&ROWS($1:1),Pickups!$H:$H,0)),IFERROR(INDEX(Other!A:A,MATCH("LON_"&ROWS($1:1),Other!$H:$H,0)),""))

This tries to find a match in the Pickups sheet for the LON_nnn code (and if not present it looks in the Other sheet), where nnn is generated by the ROWS($1:1) term which increases as the formula is copied down. If a match is found then it returns the value from column A on that row. A similar formula can be put in column C, although you will want to get data from column D of the Pickups or Other sheets, and another similar formula in column G (returning data from column F). I've amended your formula in B5 to this:

=IF(A5<>"",$A$3,"")

This row of formulae can be copied down to row 14. Similar formulae can be used for rows 17 and 29 and then copied down, although the reference to LON_ (shown in red above) will need to be changed as appropriate.

If you need more than 10 records, then just insert a few rows and copy the formulae down as required.

Hope this helps.

Pete

Thanks Ford. It works when I copy your formula and paste but what I select the cell with the formula in it and press CTRL+SHIFT+ENTER, the brackets don't change and it returns the first entry from row A5 on picksup not the row corresponding to the first DCT LON which is on row 8 on pickups. I am going to study how arrays work more thoroughly but in the meantime if you could tell me what I'm doing wrong, I would appreciate it.

Thanks again for your time.

Did you look at my solution?

Pete

Yes Pete. This absolutely works and thanks so much for all information on what the formulas are doing. This is of enormous help for I do want to understand it as well as just getting done so I can utilize the knowledge in future as opposed to coming on this forum and having some one do it for me. I may have a few additional questions for you to ensure I understand it properly.

Thanks so much.

Well, glad to be able to help - ask away when you are ready.

Also when you are ready, you might like to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

And, you can also directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

EDIT: Ah, I see you have done that - thanks for the rep.

Pete