Complicated VLOOKUP, MATCH, IF's, and INDEX question...

I

idriveatincan

New Member
Joined
Nov 20, 2017
Messages
3
I've been spending 3+ days trying multiple strains of excel formulas to solve my conundrum and need some help.

I am trying to populate quantities by location and part number on a separate excel tab. For example:

TAB 1: **DATA SAMPLE**

A B C
# Item Number Location On-Hand
1 AMP-RBLM BGB21 448
2 AMP-RBLM WF5000 1040
3 AMP-VHB1P WITOP 620
4 B011-PP WA2301 46
5 B011-PP WA2304 1774
6 B011-6-CRP BGA23 2490
7 B011-6-CRP ST01 -2429
8 B011-6-CRP WA2301 3500
9 B011-6-8NSP BGA23 1000

TAB 2: **G0AL** (USER INPUTS ITEM NUMBER AND ADDITIONAL INFORMATION AUTO POPULATES FROM TAB1:

A B C D E F G H
# Item Number Location1 On-Hand1 Location2 On-Hand2 Location3 On-Hand3 TOTAL AVAILABLE1 AMP-RBLM BGB21 448 WF5000 1040 #N/A! #N/A! 1488
2 AMP-VHB1P WITOP 620 #N/A! #N/A! #N/A! #N/A! 620
3 B011-PP WA2301 46 WA2304 1774 #N/A! #N/A! 1820
4 B011-6-CRP BGA23 2490 ST01 -2429 WA2301 3500 3561
5 B011-6-8NSP BGA23 1000 #N/A! #N/A! #N/A! #N/A! 1000

There is about 2k lines of data in the 'TAB1' spreadsheet and there will only be about 60 lines to free form in 'TAB2'. I am only concerned with populating the free-form in 'TAB2' from the information compiled in 'TAB1'.

The original thought was to VLOOKUP the part number and then pull the quantities from the location tab as classified in consecutive order. The next thought was to look up the part number and then be specific as to what location (ie. B*, W*, or ST01) but found that it would be too cumbersome and would require too many columns for the accounting department to review.

The goal is to be able to input a specific part number (ie. AMP-RBLM) and then have the data pertaining to that part number follow on the same line. I can auto sum the difference from all of the 'On-Hand2' columns to find the available totals and I can format the #N/A! to read as 0. What I need help with is taking the information (acquired from another source and then copy/paste into the 'TAB1') from 'TAB1' and dispersing it into each row.

(I attempted to take screen shots but this forum wouldn't allow it)

If you can help, I would greatly appreciate it!

Thank you,
Keven
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Hi,

take a look at this:


Book1
ABCDEFGH
1Location1On-Hand1Location2On-Hand2Location3On-Hand3TOTAL AVAILABLE
2AMP-RBLMWF50001040BGB21448  1488
3AMP-VHB1PWITOP620620
4B011-PPWA23041774WA2301461820
5B011-6-CRPWA23013500ST01-2429BGA2324903561
6B011-6-8NSPBGA2310001000
tab2
Cell Formulas
RangeFormula
H2=SUMPRODUCT((LEFT($B$1:$G$1,7)="On-hand")*(B2:G2<>""),B2:G2)
B2{=IFERROR(INDEX('tab1'!$B$2:$B$10,LARGE(IF('tab1'!$A$2:$A$10=$A2,ROW('tab1'!$B$2:$B$10),0),RIGHT(B$1,1))-1),"")}
C2{=IFERROR(INDEX('tab1'!$C$2:$C$10,LARGE(IF('tab1'!$A$2:$A$10=$A2,ROW('tab1'!$C$2:$C$10),0),RIGHT(B$1,1))-1),"")}
D2{=IFERROR(INDEX('tab1'!$B$2:$B$10,LARGE(IF('tab1'!$A$2:$A$10=$A2,ROW('tab1'!$B$2:$B$10),0),RIGHT(D$1,1))-1),"")}
E2{=IFERROR(INDEX('tab1'!$C$2:$C$10,LARGE(IF('tab1'!$A$2:$A$10=$A2,ROW('tab1'!$C$2:$C$10),0),RIGHT(D$1,1))-1),"")}
F2{=IFERROR(INDEX('tab1'!$B$2:$B$10,LARGE(IF('tab1'!$A$2:$A$10=$A2,ROW('tab1'!$B$2:$B$10),0),RIGHT(F$1,1))-1),"")}
G2{=IFERROR(INDEX('tab1'!$C$2:$C$10,LARGE(IF('tab1'!$A$2:$A$10=$A2,ROW('tab1'!$C$2:$C$10),0),RIGHT(F$1,1))-1),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
try.

Excel 2010
A
B
C
1
item num
location
onhand
2
AMP-RBLM
BGB21
448
3
AMP-RBLM
WF5000
1040
4
AMP-VHB1P
WITOP
620
5
B011-PP
WA2301
46
6
B011-PP
WA2304
1774
7
B011-6-CRP
BGA23
2490
8
B011-6-CRP
ST01
-2429
9
B011-6-CRP
WA2301
3500
10
B011-6-8NSP
BGA23
1000

<tbody>
</tbody>
Sheet1​



Excel 2010
A
B
C
D
E
F
G
H
1
item num
location1
onhand1
location2
onhand2
location3
onhand3
total available
2
AMP-RBLM
BGB21
448
WF5000
1040
1488
3
AMP-VHB1P
WITOP
620
620
4
B011-PP
WA2301
46
WA2304
1774
1820
5
B011-6-CRP
BGA23
2490
ST01
-2429
WA2301
3500
3561
6
B011-6-8NSP
BGA23
1000
1000

<tbody>
</tbody>
Sheet2​


Worksheet Formulas
Cell
Formula
H2
=SUM(C2,E2,G2)
H3
=SUM(C3,E3,G3)
H4
=SUM(C4,E4,G4)
H5
=SUM(C5,E5,G5)
H6
=SUM(C6,E6,G6)

<tbody>
</tbody>

<tbody>
</tbody>

Note since your sample shows only 3 locations I use the number in the header to get the K value for the small. If your locations go double digit then this will not work. You can replace the right function with the number.

Array Formulas
Cell
Formula
B2
{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}
C2
{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}
D2
{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}
E2
{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}
F2
{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}
G2
{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}
B3
{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}
C3
{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}
D3
{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}
E3
{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}
F3
{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}
G3
{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}
B4
{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}
C4
{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}
D4
{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}
E4
{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}
F4
{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}
G4
{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}
B5
{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}
C5
{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}
D5
{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}
E5
{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}
F5
{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}
G5
{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}
B6
{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}
C6
{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}
D6
{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}
E6
{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}
F6
{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}
G6
{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
 
Upvote 0
Thank you, gentlemen! I will play with it later this morning and let you know how it goes. You have been a great help!

V/r
Keven

Scott T said:
try.

Excel 2010
ABC
1item numlocationonhand
2AMP-RBLMBGB21448
3AMP-RBLMWF50001040
4AMP-VHB1PWITOP620
5B011-PPWA230146
6B011-PPWA23041774
7B011-6-CRPBGA232490
8B011-6-CRPST01-2429
9B011-6-CRPWA23013500
10B011-6-8NSPBGA231000

<tbody>
</tbody>
Sheet1​



Excel 2010
ABCDEFGH
1item numlocation1onhand1location2onhand2location3onhand3total available
2AMP-RBLMBGB21448WF500010401488
3AMP-VHB1PWITOP620620
4B011-PPWA230146WA230417741820
5B011-6-CRPBGA232490ST01-2429WA230135003561
6B011-6-8NSPBGA2310001000

<tbody>
</tbody>
Sheet2​

Worksheet Formulas
CellFormula
H2=SUM(C2,E2,G2)
H3=SUM(C3,E3,G3)
H4=SUM(C4,E4,G4)
H5=SUM(C5,E5,G5)
H6=SUM(C6,E6,G6)

<tbody>
</tbody>

<tbody>
</tbody>

Note since your sample shows only 3 locations I use the number in the header to get the K value for the small. If your locations go double digit then this will not work. You can replace the right function with the number.
Array Formulas
CellFormula
B2{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}
C2{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}
D2{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}
E2{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}
F2{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}
G2{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A2=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}
B3{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}
C3{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}
D3{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}
E3{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}
F3{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}
G3{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A3=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}
B4{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}
C4{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}
D4{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}
E4{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}
F4{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}
G4{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A4=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}
B5{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}
C5{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}
D5{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}
E5{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}
F5{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}
G5{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A5=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}
B6{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(B$1,1))),"")}
C6{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(C$1,1)))+0,"")}
D6{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(D$1,1))),"")}
E6{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(E$1,1)))+0,"")}
F6{=IFERROR(INDEX(Sheet1!$B$2:$B$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(F$1,1))),"")}
G6{=IFERROR(INDEX(Sheet1!$C$2:$C$10,SMALL(IF($A6=Sheet1!$A$2:$A$10,ROW(Sheet1!$B$2:$B$10)-ROW(Sheet1!$B$2)+1),RIGHT(G$1,1))),"")}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
Click to expand...
 
Upvote 0
Gentlemen,

I attempted your solutions in a new spreadsheet and had several errors:

Sheet1
ABCDEFGH
1PART NO.LOCATION1ON-HAND1LOCATION2ON-HAND2LOCATION3ON-HAND3TOTAL
2AMP-RBLMBGB21448448
3AMP-VHB1PWF5000
10401040
4B011-PPWITOP620620
5B011-6-CRPWA23014646
6B011-6-8NSPWA230417741774


<tbody>
</tbody>

Instead of having the location and quantity data spread across the row, it is following down the column, skewing the data and causing the information to be displayed incorrectly.

The second solution only allowed information in the first location/on-hand cells, nothing else worked.

I also attempted to replace/free-form the part number by replacing the RBLM with the VHB1P part number. The information following it did not update. Am I missing something?

This is the goal:

Sheet1
ABCDEFGH
1PART NO.LOCATION1ON-HAND1LOCATION2ON-HAND2LOCATION3ON-HAND3TOTAL
2AMP-RBLMBGB21448WF50001040N/A01488
3AMP-VHB1PWITOP620N/A0
N/A0620
4B011-PPWA230146WA23041774N/A01820
5B011-6-CRPBGA232490ST01-2429WA230135003561
6B011-6-8NSPBGA231000N/A0N/A01000

<tbody>
</tbody>

If the cell returns the data as an error or #N/A!, I am able to change it to 'N/A' or 0. I don't need help with that.

Thank you, again for your help - it is greatly appreciated.

V/r
Keven
 
Upvote 0
Did you use Control+Shift+Enter for the array formulas?
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,452
Messages
6,124,914
Members
449,195
Latest member
Stevenciu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top