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

[curiosity]

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

 

[Peter_SSs]

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]

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
	A	B	C	D	E	F
1	partslist	customer	purchased	description	paid	cost
2	123	johnbiggs	125	motorcycle	$10.00	$8.00
3	124
4	125
5	126
6	127
7	128
8	129
9	130
Sheet1

 

[Peter_SSs]

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]

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]

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]

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]

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>