Formula or Macro needed?

P

PvtSmity

New Member
Joined
Jul 26, 2016
Messages
13
Hi All,
Ok so I have been working on a spread sheet for my work and I am trying to have excel go down 2 columns(S & Y) and pull only the first entry of each name and have them separated in row 1, columns C through I. Because of the information on this spreadsheet, I can only give the data in the columns I need help with.
I need it to only pull 1 of the 07-08-00-1 and then go down the list and pull NA and so forth. I added column Y in the mix because sometimes it has different information then column S. If someone would be able to help me with this, it would be wonderful and appreciated.

[TABLE="width: 224"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Column S[/TD]
[TD]Column Y[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]07-08-00-1[/TD]
[/TR]
[TR]
[TD]07-08-00-1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07-08-00-1[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]55-5[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07-08-00-1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07-08-00-1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]KIT-INT[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]55-5[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07-08-00-1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07-08-00-1[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]55-5[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07-08-00-1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]07-08-00-1[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NA[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Sorry, it's just not clear (to me at least) what you want exactly.

Please explain why you are selecting the items shown.
It doesn't make any sense to me (yet !)
 
Upvote 0
Gerald Higgins said:
Sorry, it's just not clear (to me at least) what you want exactly.

Please explain why you are selecting the items shown.
It doesn't make any sense to me (yet !)
Click to expand...

Hi Gerald,

Sorry about that. So this report is based off of movement for my company on materials from one location to another. I need to be able to have excel go through column S, find the first bin and paste it into C1 then continue down the column and find the next bin ignoring the duplicates and pasting the next bin into D1 until there is no more data in that column. then go to column Y and repeat the process.

In the report I have downloaded to excel column S and Y are always the same kind of data with bin information.
The formula I have right now to help with inventory movement is =IF(C$1=S5,-$K5,IF(C$1=Y5,-$K5,""))
instead of trying to find each different bin in both columns and paste them in to row 1, i was hoping that excel could do that and eliminate the possibility of missing a bin location.
 
Upvote 0
Sorry but post #3 doesn't help me at all.

Please post a small sample of your data, and explain what the results should be and why.
If you don't want to post real data, make some up.
 
Upvote 0
So the end result should be, all different bins are found in column S & Y and are placed in C1 through F1, only if there are 4 different bins found. The formula I am using is =IF(C$2=S7,-$K7,IF(C$2=Y7,-$K7,"")) cause I need it to give a total per transaction from 1 bin to another bin so I can pin point when a discrepancy happens. If there is a better to display the data please let me know.


[TABLE="width: 868"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]K[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]07-08-00-1[/TD]
[TD]NA[/TD]
[TD]KIT-INT[/TD]
[TD]55-5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ABS TOTAL[/TD]
[TD]Fpartno[/TD]
[TD]Ftype[/TD]
[TD]Fqty[/TD]
[TD]Ffromloc[/TD]
[TD]Ffrombin[/TD]
[TD]Ftoloc[/TD]
[TD]Ftobin[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]`[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]475.00000[/TD]
[TD="align: right"]842[/TD]
[TD]H[/TD]
[TD="align: right"]475.00000[/TD]
[TD]03[/TD]
[TD][/TD]
[TD]03[/TD]
[TD]07-08-00-1[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]-6.00000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6.00000[/TD]
[TD="align: right"]842[/TD]
[TD]I[/TD]
[TD="align: right"]-6.00000[/TD]
[TD]03[/TD]
[TD]07-08-00-1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]-320.00000[/TD]
[TD="align: right"]320.00000[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]320.00000[/TD]
[TD="align: right"]842[/TD]
[TD]T[/TD]
[TD="align: right"]320.00000[/TD]
[TD]03[/TD]
[TD]07-08-00-1[/TD]
[TD]01[/TD]
[TD]NA[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD="align: right"]-40.00000[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40.00000[/TD]
[TD="align: right"]842[/TD]
[TD]I[/TD]
[TD="align: right"]-40.00000[/TD]
[TD]01[/TD]
[TD]NA[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]40.00000[/TD]
[TD="align: right"]842[/TD]
[TD]I[/TD]
[TD="align: right"]-40.00000[/TD]
[TD]02[/TD]
[TD]55-5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="align: right"]-24.00000[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]24.00000[/TD]
[TD="align: right"]842[/TD]
[TD]I[/TD]
[TD="align: right"]-24.00000[/TD]
[TD]01[/TD]
[TD]NA[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]-4.00000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.00000[/TD]
[TD="align: right"]842[/TD]
[TD]I[/TD]
[TD="align: right"]-4.00000[/TD]
[TD]03[/TD]
[TD]07-08-00-1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]-4.00000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.00000[/TD]
[TD="align: right"]842[/TD]
[TD]I[/TD]
[TD="align: right"]-4.00000[/TD]
[TD]03[/TD]
[TD]07-08-00-1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Ok nevermind on this. I think I am going at this the wrong way. I just need it to pull the unique number from 2 columns to 1 list.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
199
Tara_A_Data
T
Z
  • Question Question
Formula help Need SUMIFS for a column with starting numbers
Replies
3
Views
126
zone709
Z
welshgasman
  • Question Question
Formulae jumping references?
Replies
7
Views
101
welshgasman
welshgasman
K
  • Question Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
607
awoohaw
awoohaw

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

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