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
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