Finding the right way in excel

[jmendez]

I need to have control within the warehouse of the orders being processed day by day, the old existing software provided only two columns of information: Order number and Date. Once I received all the orders I scanned one by one and placed that information in to a spreadsheet, “as incoming”.I add a 3<SUP>rd</SUP> column to put the names of the persons that will work on that order. At the end of the day, just the orders that are finished are scanned again as ”outgoing” and the information is placed into the same spreadsheet in a 4<SUP>th</SUP> and 5ht column
I N C O MI N G O U T G O I N G
<TABLE class=MsoNormalTable style="MARGIN: auto auto auto 4.65pt; WIDTH: 5.5in; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" cellSpacing=0 cellPadding=0 width=528 border=0><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: windowtext 1pt solid; WIDTH: 80pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-alt: solid windowtext .5pt" vAlign=bottom noWrap width=107>

ORDER NUMBER<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 51pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=68>

DATE<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 137pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=183>

ASSIGNED TO<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 80pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=107>

ORDER NUMBER<o></o>​

</TD><TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 1pt solid; PADDING-LEFT: 5.4pt; PADDING-BOTTOM: 0in; BORDER-LEFT: #ece9d8; WIDTH: 48pt; PADDING-TOP: 0in; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" vAlign=bottom noWrap width=64>

DATE<o></o>​

</TD></TR></TBODY></TABLE>
<o> </o>
My question is: How Can I make in a automated way that all the information in the outgoing columns that match the orders numbers of the first column will go to the designated row
Remember that all the orders that is given to the warehouse personal are not finished the same day ,

 

[BGY23]

Assuming that your order numbers are unique i.e. only occur once in your first column.

You can insert columns between your 3rd and 4th columns and do a vlookup on the order number in the first column against the orders numbers scanned as outgoing.

Lets say incoming orders are in a1 to c200. Out going orders are now in H1 to I180 (20 not yet complete)

in D2 type this formula Vlookup($a2, $h$1:$I180,1,false)
in E2 type Vlookup($a2, $h$1:$I180,2,false)

The first one will give the order number and the second formula the date. Unfortunately you get #Value if the order number isn't in your outgoing list. You can either delete them or write an if statement to stop the #values

 

[jmendez]

That was quick!!! Thanks so much. I'll try that tonight