Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Move data from a column of row couplets to multiple rows of colum

  Asked By: Ketan    Date: Jan 21    Category: MS Office    Views: 2096
  

I am very new to VBA; played around with recording some macros and
worked through the first couple of lessons Mark Thorpe put together
in Techtrax "no-experience Necessary" library.

I have seen some posts on "transposing" data but haven't been able to
figure out how to solve my problem.

I'm trying to reorganize a spreadsheet of contacts. The contacts are
arranged as a single column of row couplets (i.e. For contact "A":
Name "A" cell ( x , 1 ) Information "A"(contact person, mailing
address, phone number, etc.) cell ( x+1,1 ); For contact "B":
Name "B" cell ( x+2 , 1) Information "B" ( x+3 , 1); etc, etc, X
3000+/- rows of data). I want to move the Company Information cell
up one row and right one column and delete the blank row that will
remain and repeat through all occupied cells.

After I complete this shift of cells and delete the blank rows it
will be a simple matter to split the new column (2) as the company
information data is comma delimited.

I thank whoever can help me in advance and would happily buy you a
pint of guiness down at Kells if you live in Seattle.

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Charlie Evans     Answered On: Jan 21

Assume worksheet containing the data  to be reorganized is
named "HaveAPint". Put the following sub in any code module, for
example the ThisWorkbook module:

Public Sub HaveAGuinnessInSeattle()
With Worksheets("HaveAPint")
.activate
.cells(1,1).select
do until isempty(activecell.value)
.cells(activecell.row,2).value = .cells(activecell.row + 1,1).value
activecell.offset(1,0).select
activecell.entirerow.delete
loop
end with
end sub

 
Answer #2    Answered By: Jawna Mohammad     Answered On: Jan 21

Insert 1 cell  at the top of the column
Select the whole of the column  below the blank
Copy
Move to the cell to the right of the blank  column
Paste
Move to the next column to the right and type =MOD(ROW(),2)
Copy this formula all the way down


Then select the 3 columns and go
Data/Filter/Autofilter
Click the little arrow above the new column (which contains 1s and 0s)
Select 1 or zero (whichever is opposite the second data  row)
Select all the visible data, Right click, and select delete  row
Confirm
Remove the autofilter

 
Answer #3    Answered By: Venkat Rulez     Answered On: Jan 21

this is transposing too but repeated values arent repeated.
I fancy you can help  me here.

suppose i have data  in the form;
there are 2 columns on which i want to apply a code or macro.


cell_id ........neighbour_ id

1 ............ ..2
1 ............ ...3
1 ............ ...4
2 ............ ...1
2 ............ ...3
3 ............ ...1
3 ............ ...2
3 ............ ...4



this is the output i am interested in:

cell_id neighbour_id1 neighbour_id2 neighbour_id3
1 ...........2 ...........3 ..........4
2 ...........1 ...........3
3 ...........1 ...........2 ..........4

can anyone help me .
ps note;there are 4 columns in the output.
Macro would be preferred or you can tell me using VBA.

 
Answer #4    Answered By: Minal Nayak     Answered On: Jan 21

Using a Pivot Table would make short work of this.

 




Tagged: