Cross Workbook equation to get data into another format (dealing with duplicate numbers)

katie97

New Member
I have an IF statement pulling date onto Sheet 1 (=IF('Sheet 2'!$E$4=H15,'Sheet2'!$C$4,False). However, I have some numbers in the E column that are identical, while the respective values in row C are different. Is there a way to tell excel to move onto the next instance of the number in column E? For example, if it has already pulled C4 for E4, can I tell it to move on to E5:$E to search for the next occurrence and then pull the respective number from column C?

I realize an IF statement may not be best for this, but I'm at the end of my excel knowledge, so any help would be greatly appreciated! :)

Link to screenshots: https://drive.google.com/drive/folders/15gkI3-eED9mesOvanVcNLZiFjYWWuzSU?usp=sharing
 

Sam_D_Ben

Active Member
I guess you are looking for this.

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(102,0,0)"><colgroup><col width="25px" style="background-color: rgb(255,255,255)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(255,255,255);text-align: center;color: rgb(102,0,0)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(102,0,0);text-align: center;">1</td><td style="text-align: center;;">LINE#</td><td style="text-align: center;;"></td><td style="text-align: center;;">LINE</td><td style="text-align: center;;"></td><td style="text-align: center;;">PO#</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">PO Number</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">2</td><td style="text-align: center;;">111</td><td style="text-align: center;;"></td><td style="text-align: center;;">111</td><td style="text-align: center;;"></td><td style="text-align: center;;">2019-40</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">2019-40</td></tr><tr ><td style="color: rgb(102,0,0);text-align: center;">3</td><td style="text-align: center;;">112</td><td style="text-align: center;;"></td><td style="text-align: center;;">112</td><td style="text-align: center;;"></td><td style="text-align: center;;">2019-40</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(102,0,0);border-top:none;text-align: center;background-color: rgb(255,255,255);color: rgb(102,0,0)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(102,0,0)"><thead><tr style=" background-color: rgb(255,255,255);color: rgb(102,0,0)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(255,255,255);color: rgb(102,0,0)">A2</th><td style="text-align:left">=IF(<font color="Blue">$E2=$H$2,$C2,FALSE</font>)</td></tr></tbody></table></td></tr></table><br />
 

Peter_SSs

MrExcel MVP, Moderator
Welcome to the MrExcel board!

Try these

In cell B34 of the formula sheet:

=IFERROR(INDEX('PO Log'!C$4:C$100,AGGREGATE(15,6,(ROW('PO Log'!E$4:E$100)-ROW('PO Log'!E$4)+1)/('PO Log'!$E$4:$E$100=$H$15),$A34)),"")

then, after adjusting the $100s for the end of your data (if required) copy/paste this formula to B36, B38 etc


In cell E34 of the formula sheet

=IF($B34="","",INDEX('PO Log'!B$4:B$100,AGGREGATE(15,6,(ROW('PO Log'!B$4:B$100)-ROW('PO Log'!B$4)+1)/('PO Log'!$E$4:$E$100=$H$15),$A34))&"")

adjust & copy to rows 36, 38, ...

You should then be able to copy the E34 formula to Z34 & try adjusting it to point to the correct column. Post back if you need more help with that part or the above.

BTW, it looks like you might be using a Mac version of Excel. If so, always a good idea to state that & your operating system as the majority of helpers (including me) will normally assume PC versions.
 
Last edited:

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top