Transposition for a whole sheet? problem with all vertical paste series

  • Thread starter Thread starter hoffman3
  • Start date Start date
H

hoffman3

Alright here's the problem, I have a huge copy and pasted database tha
pastes in one column with name, date, job, etc. stacked. Is there an
way to transpose all of those into horizontal and vertical columns.
can transpose them one by one but that takes forever. Also anothe
part, say i have these and want to pull one thread from each and lin
it up against the name, what kind of formula would i need.
thanks a lot
hoffman
 
More detail needed please - Consistent number of rows per record? / How many
rows per record?
 
Hi ........,
If you have something like address labels and you want to
convert that into rows with name, address..., town, zip
see Creating a Table from something like a column of address labels (#snkAddr)
http://www.mvps.org/dmcritchie/excel/snakecol.htm#snkAddr
there is also a worksheet solution in the topic after that one.

Creating a Spreadsheet from Database data (#dbdata)
http://www.mvps.org/dmcritchie/excel/snakecol.htm#dbdata

Please use your first and last name in technical newsgroups.
 
Thanks for the two replies, here are the problems. When I copy an
paste the directory listing into excel the records don't have
consistent number of rows b/c some of the people in the director
didn't fill in certain information so then sometimes they have 7 row
and sometime as many as 11. Also the entries don't have two column
with one column of headings such as name, birthdate, email or anythin
like that, it's all jammed into the one column. If anyone can help ou
please let me know. Thanks again.
Eric Hoffma
 
A computer is like any other machine and needs guidelines to work with. Unless
there is some logical way of determining the breaks between the sets of records,
it is going to be extremely difficult to do this. Do you ahe any logical
breaks, ie maybe a blank row between each set of records, maybe each set of
records starts with a field heading of say 'name'. Maybe you could have the
database that spat this out insert a dummy piece of data in all the blank
records so that they are all 11 rows deep, or something similar. If the only
way is to trawl through using human judgement on whether the record being looked
at is the first of that set then I think you will struggle.

Maybe you can get a dump of just the names and then use these in conjunction
with a macro to break every time it hits a record that appears in a pre-defined
set of names?
 
I have all the data squished together now and the names are listed jus
below a standard identifier, ISLN: *********, I've tried to use a
OFFSET with MATCH function but it will never return anything, jus
"#Name?". This is the function I've thrown in, OFFSET(A(MATCH("ISLN
*********", A1:A100,0)),1,0). I'm sure the MATCH statement is right b/
if i put it out seperately then it'll return how many cells down i
finds the string from where it's located. Thanks for the help.
Eric Hoffma
 
Use index

=INDEX(A1:A100,MATCH(etc...))

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Assuming your data looks like this then:-

ISLN:- ********
S
T
M
N
X
H
ISLN:- ********
N
S
D
K
V
T
J
ISLN:- ********
W
T


and also assuming it starts in row 2 with ISLN:- ******** being in A2, run the
following routine on a copy of your workbook:-



Sub MoveData()

Dim r As Long
Dim rowval As Long
Dim cnt As Long
Dim lrow As Long

Application.ScreenUpdating = False

lrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
cnt = 1

For r = 2 To lrow
With Cells(r, "A")
If Left(.Value, 4) = "ISLN" Then
rowval = r
cnt = cnt + 1
End If

If r <> rowval Then
.Copy Cells(cnt, "A").Offset(0, r - rowval)
End If
End With
Next r

Application.ScreenUpdating = True

End Sub
 
Thank you so much for that code, it seriously took out hours of worktim
that i was planning on doing. It is almost flawless. the only proble
i'm having is that when someone didn't fill out a field all th
following fields in the row fall into the wrong column. I'm stil
trying to brainstorm of a way to do that, but thank you so very muc
for your time and effort on this.
Eric Hoffma
 
No problem, but don't leave before we fix it. Give me some more details on how
what they do affects what the code does. What goes wrong exactly, and is there
a way of flagging the errors?
 
Alright the problem is that with the data that I'm throwing into th
rows doesn't have field names except for a few fields. When someon
didn't fill out the field then all the information shifts one cel
left. The only thing i could even imagine about doing is insertin
blank rows when a field is not answered, which i think may be a bit o
a challenge. Luckily the optional rows all start with identifiers, ex
biography:, Education:, ...
The second part is, sometimes there is information between the ISNL
and the name so i was wondering if there is a code to find a word in
row then delete the entire cell and if i'd be able to put multipl
look-up words in it b/c there are only certain fields that can g
between the # and the name and they all have identifiers. If you nee
any more info then just let me know. I'm gonna be brainstorming tonigh
to think of somemore ways but thanks for all the help, muc
appreciated.
Eric Hoffma
 
It may well be easier to do a bit of data cleansing initially rather than start
trying to build it into the code, eg have a list of the codes you mention that
you want deleted on another sheet, put in a helper column on your data sheet and
then use a formula to flag that row as "Delete" if the cell in that row contains
any of the words found in your table on sheet 2.

Example:-

ISLN:- ********
xyz:-
def:-
M
N
X
H
ISLN:- ********
def:-
xyz:-
def:-
K
V
T
J
ISLN:- ********
xyz:-
T

On sheet 2 you have a table in say A1:A50 with your codes in

xyz:-
def:-
abc:-
fde:-
cvd:-
etc

Then back on sheet1 with your data, assuming it is all in Col A still, in cell
B1 put the following and copy down:-

=COUNTIF(Sheet2!$A$1:$A$5,Sheet1!A1)

Now just select Col B and do data / Filter / Autofilter, filter on 1, then
select the column, do Edit / Go To / Special / Visible cells only and then Do
Edit / Delete / Entire Row.

Now just run the code I gave you before.

What this won't do is line up your missing fields but I have no idea how you do
that without manually verifying each one.
 
Alright the formula you're giving me doesn't seem to be working quite
right, it's not numbering the ones i've listed. It will number some of
them but then at the same time it'll number ones that i don't have on
the sheet two list. Not sure where to go from here. Let me know if you
have any advice.
Eric Hoffman
 
Alright I fixed the last problem but the search only looks for exac
matches and these fields only have one word in common. Is there a wa
to mark if the word exists in the cell instead of the exact match i
the cell?
Eric Hoffma
 
Give me a small sample of your data in the note - Just paste it in as text.
Then also a sample of your codes.
 
Back
Top