Is it possible to move a cell based on a value/match

curiosity

New Member
Joined
Oct 11, 2006
Messages
9
G'day all,

Ok is it possible to do the following in excel - thanks in advance for any and all assistance.

I have a work book which has a master column of part numbers - then I have customer name part number purchased qty dollar value and a gross value

The customer

col 1 col 2 col 3 col 4 col 5 col 6

part numbers - customer - part no purch - qty - dollar value - gross value

1234 john 4321 1 $2 $4
4321

Is it possible to match by part no in column 3 to master part number in column 1 and to select col 2, 3, 4, 5, 6 and have that data moved to match row number two 4321??

I hope what Im asking is understandable :)

I have a report that I need to build which involves looking at customer purchase history which gives me the goods they have purchased quantity dollar value - I need to match the part number and move it and its adjacent data to match the master column part number - so that I can latter build a history of purchased items by customer.


Thanks again in advance hopefully Im not asking too much..


Cheers
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,424
Office Version
  1. 365
Platform
  1. Windows
curiosity

Welcome to the Mr Excel board!

Sorry, it is not clear to me. I suggest you download, install and learn to use Colo's HTML Maker for displaying samples of your sheet on the board. Here's how:
http://www.mrexcel.com/board2/viewtopic.php?t=92622

Then perhaps you can show us a 'before' and 'after' screen shot to better explain what you want.
 

curiosity

New Member
Joined
Oct 11, 2006
Messages
9
Peter with many thanks !!!!

Ok - great little addin with thanks - ok lets try now - ok you will see that I have parts list customer purchased description paid cost

is is possible to match the purchased 125 to the parts list 125 and move the row B2,C2,D2,E2,F2 To row B4,C4,D4,E4,F4.

My spread sheet contains just over 500 part numbers - rather than manually shifting cells down to match the part number I am looking at some way to automate the process :) - or am I asking too much of excel.

Each customer only purchases of course a handfull of products.

Cheers,

Steve
Book1
ABCDEF
1partslistcustomerpurchaseddescriptionpaidcost
2123johnbiggs125motorcycle$10.00$8.00
3124
4125
5126
6127
7128
8129
9130
Sheet1
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,424
Office Version
  1. 365
Platform
  1. Windows
OK, let's assume we have moved the cells down to row 4. What happens if the next customer, Fred Derf purchases part 125 motor cycle paid $20 cost $16? I assume this data would initially be entered in row 2 again, but where would it move to? ..or would john biggs and his data get overwritten?
 

curiosity

New Member
Joined
Oct 11, 2006
Messages
9

ADVERTISEMENT

Ok I should explain further - sorry - there is only one sheet per customer...

Each customer is looked at separately so there would never be a merging of cells.

only 150 odd customers - I intend to then sum on an index page at some other stage.

Regards

Steve
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,424
Office Version
  1. 365
Platform
  1. Windows
Ok I should explain further - sorry - there is only one sheet per customer...

Each customer is looked at separately so there would never be a merging of cells.

only 150 odd customers - I intend to then sum on an index page at some other stage.

Regards

Steve
In that case, what happens if john biggs buys another motor cycle (or three) later?
 

curiosity

New Member
Joined
Oct 11, 2006
Messages
9
Ok to further add to the picture - this data is drawn from previous months

so I am comparing feb march april may june etc etc - there is no possibility of the number of items purchased increasing - I am looking at past history...


Cheers

Steve
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,424
Office Version
  1. 365
Platform
  1. Windows
Steve

VBA is not my strong point so I don't know if this is the best way to attack this but see if this is the sort of thing you want.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> MoveRow()
    <SPAN style="color:#00007F">Dim</SPAN> LastSelection <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> LastActiveCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> PartNo <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    
    Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    LastSelection = Selection.Address
    LastActiveCell = ActiveCell.Address
    PartNo = Range("C2").Value
    Columns("A:A").Select
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> ErrorTrap
    Selection.Find(What:=PartNo, After:=Range("A1"), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
    Range("B2:F2").Cut
    ActiveCell.Offset(0, 1).Select
    ActiveSheet.Paste
PreExit:
    Range(LastSelection).Select
    Range(LastActiveCell).Activate
    Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
ErrorTrap:
    MsgBox "Part Number not found in Parts List", vbOK, "Error"
    <SPAN style="color:#00007F">Resume</SPAN> PreExit
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

Forum statistics

Threads
1,136,349
Messages
5,675,244
Members
419,557
Latest member
razlevav

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
Top