Excel help

[Poweruser99]

Here is my problem which has me going in circles. I have a spread sheet with user data. This sheet is structured in a table format. There are 20,000 records.

Basically it is a table of users and the money they have spent. There are fields which specify the place money spent the amount spent and other details.
This is how it is structured.

NAME: ABCEFG

AMOUNT PLACE QUANTITY ETC
55 Maine 55
66 Tampa 23
69 NYC 65

The above would be a sample record for a user. I have over a 1000 users. What I would like to have is to copy the name field and make it into a seperate field like AMOUNT or PLACE. Copy and pasteing it manually would take forever. Also for each user, the source field to be copied from is different. Any ideas would be appreciated.

 

[TonyJollans]

Hi Poweruser99,

Is this a one-off exercise? Or do you want some code to be able to run repeatedly?

Also, could you post a bit more detail about the format - specifically individual cell contents - for example is "NAME: ABCEFG" in one cell or two? Are there repeated headings? Are there blank lines? etc.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[SkipVought]

Hi,

This sheet is structured in a table format.

Your sheet is NOT in table format. THAT'S THE PROBLEM.

The answer depends on how CONSISTENT the data FORMAT is.

It could be as simple as this kind of process...
[tt]
Insert a new sheet for the results named wsNew

For Each Row in TheActiveSheetUsedRange.ColumnA
If (TheFirstSixCharacters.Value = "NAME: ") Then
TheName = TheRestOfTheCharacters
Otherwise
If (.Value = "AMOUNT") Then
'this is a heading row not data
If (This is the first time) Then
Write headings to wsNew ALONG WITH "NAME"
End
Otherwise
'this is a data row not heading
Write the data for each column of this row to wsNew, INCLUDING TheName
End
End
Next Row
[/tt]
Do you need help writing this kind of code?

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[Poweruser99]

This is an excercise for 20,000 records. The name field is repeated through out and its different for each user. Basically the sheet looks like this:

NAME: ABCEFG

AMOUNT PLACE QUANTITY ETC
55 Maine 55
66 Tampa 23
69 NYC 65

So the name is a user and his account details follow below. There are over a thousand users. This is the way I want it to be.

NAME AMOUNT PLACE QUANTITY
ABCEFG 55 Maine 55
ABCEFG 66 Tampa 23
ABCEFG 69 NYC 65

As you can see I can always copy and paste but that would take forever. And also the name cells source changes for every user.

I hope this clarifies.

 

[Poweruser99]

SkipVought can you help me with this code writing??

Thanks

 

[SkipVought]

Are AMOUNT, PLACE, QUANTITY etc in separate columns?

Skip,

_{[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM!}

 

[TonyJollans]

Hi Poweruser99,

I'm still not sure what data are in what cells. Assuming:

[tt]
+--------+--------+
| NAME: | ABCEFG |
+--------+--------+
| | |
+--------+--------+--------- ...
| AMOUNT | PLACE | QUANTITY ...
+--------+--------+---------
| 55 | Maine |
+--------+--------+
| 66 | Tampa |
+--------+--------+
| | |
+--------+--------+
| NAME: | ABCEFG |
+--------+--------+
| | |
+--------+--------+--------- ...
| AMOUNT | PLACE | QUANTITY ...
+--------+--------+---------
| 55 | Maine |
+--------+--------+
| 66 | Tampa |
+--------+--------+
| | |
+--------+--------+
[/tt]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[TonyJollans]

Hi Poweruser99,

Sorry pressed button by mistake.

Anyway, assuming that format ...

Insert two columns to the left of column A
In the new A[red]2[/red] put [blue]=IF(C2="NAME:",D2,A1)[/blue]
Copy it into A1 (it will show with a #REF! in the formula but don't worry)
Copy it down as far as your data go.
In the new B1 put [blue]=IF(C1="NAME:","",IF(C1="AMOUNT","NAME",IF(ISBLANK(C1),"",A1)))[/blue]
Copy it down as far as your data go.
Copy Column B
Paste special > Values to Column B (i.e over itself)
Delete Column A.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[TonyJollans]

Oh, and use a filter afterwards to delete any rows you don't want.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Poweruser99]

Hi TonyJollans,

The format you came up with is indeed correct. I will try the method suggested by you.

Thanks

 

[Poweruser99]

Is there anyway to check using the 'LIKE' clause with the if statments. Because the column I will look up is like this: ' CARDHOLDER NAME: John Doe '

As expected the user names will change with the term CARDHOLDER NAME staying the same.

 

[TonyJollans]

Hi Poweruser99,

Instead of ..

[tt] =IF(C2="NAME:",D2,A1)[/tt]

.. use ..

[tt] =IF([red]ISERR(FIND("NAME:",C2))[/red],A1,D2)[/tt]

Note that this actually checks for "NAME:" NOT being found so I have reversed the positions of the last two parameters.

The other formula will need changing to ..

[blue][tt] =IF([red]ISERR(FIND("NAME:",C1))[/red],IF(C1="AMOUNT","NAME",IF(ISBLANK(C1),"",A1)),"")[/tt][/blue]

Your example appears to show the name being in the same cell as "NAME:" (or whatever); if that is the case you will need further changes - come back if so.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[