[RESOLVED] [EXCEL] using VBA copy one field from a column and concatenate with transpose

Hi All,

I am fairly new to VBA and I needed some help.

There is a data Set with one column as id and has different nos.

then, I have a row, with different names.

Now, I want to copy these ids first, into a new sheet and then copy the names one by one into the new sheet and concatenate the id + name together.

For e.g.

Id yellow blue orange
1 25 74 56
2 34 64 46
3 45 49 99
4 12 45 11
5 10 16 22
6 20 13 13
7 48 19 14

the columns are not consecutive as well.

What I want to do is:-

1yellow - 25
2yellow - 34
3yellow - 45
4yellow - 12
.
.
.

1blue - 74
2blue - 64
3blue - 49
4blue - 45
5blue - 16
6blue - 13

likewise for orange.

This all needs to be done, from a separate file where I have a command button, I press the button and then the data is pulled from file 1 which has yellow, blue and orange data, and then pushed into a new file say test.xls

both files are closed and have separate sheets as well.

Please let me know or help me with the code.
I am good with C++ bt never really worked with VB, worked with VC++ nd other languages.

so, all, i need is, if someone can help me with the code.

Many thanks,
Unknown

there would be several ways to do this

regardless of the code what would you consider to be the simplest method to achieve the result?

The simplest way to do it wud be,

to copy the id's first into a new sheet.
and take the row data into the same sheet.

viz.

1yelow
2blue
3orange
4
5
6
7


now, multiply them one by one into a new sheet viz.

1yellow
2yellow
3yellow
4yellow
.
.
.

and the pull value from the main file corresponding to 1 yellow.
we need not worry about order, since the order will remain the same if we copy them as is.

but, how do i put this in VBA code??

anyone???

you want to go from a column based expression of the data to a single column of "id-tagged values"

is that correct?

yeah,

But not exactly what you said.

I am attaching a file, it would make things clear.

of 2 column of data as

id-tagged thing and the other the actual data

(row with id ) (red infocolumn =value)(green info column =value)
to
red ids and values
..
..

green ids and values
..
..

like that?

yes,

concatenate(ids + red) in one column and the corresponding values against it.

And put it into a new sheet.

next questions then...

where in new sheet, what is sheet name, do colours go on different sheets, are there a known number of colours and rows or is every thing quite arbitrary?

Saved in a new workbook actually, which is just an interim one, since its going to be used for various processes and then closed eventually outputting the results into a third file.

No. of rows can vary and so can the no. of columns.
the sheet name can be anything, doesnt matter.

actually, its just a part of a very big calculation being performed every month, taking into account data from previous and current month.

this is jst like 5% of the whole project.

so, all i need to do is, put them into a new sheet and then progress from there.

there are always other ways to do these things!

anyway this solution requires a function that can be told a few factors rather than one that works out what to do, the first version will be a sight easier to construct.

i can develop the pseudo code first

and if it makes sense WE can build the code

how does that grab you?

makes sense...

I was wondering, is it beneficial to call functions in a subroutine or make various subroutines ??

bt yeah.... if u can give sme pseudo code... i can tweak nd play around wid it...

am asking this... coz am using a form for the big program, and already have a few subroutines.... was wondering, which is the best way to optimize code and efficiency....

use more functions or subroutines ??

here is a bit of code that works.. to get at the data

Code:

---

Sub translate_group_data(id As String, first_row As Integer, last_row As Integer, first_data As String, last_data As String, step_size As Integer)
'fix column values
fd = Range(first_data & "1").Column
ld = Range(last_data & "1").Column

For Group = fd To ld Step step_size
For r = first_row To last_row


Debug.Print Range(id & r).Value & Cells(2, Group - 1).Value & vbTab & Cells(r, Group).Value

Next r
Next Group
End Sub

---

you need to calll it like this... based on your example.xls

Code:

---

translate_group_data "a",4,6,"c","i",3

---

as you see it can access the appropriate information, now we just need to output the data...

proof of concept run in immediate window

Quote:

---

translate_group_data "a",4,6,"c","i",3
123RED 879
456RED 759
789RED 459
123Yellow 457
456Yellow 427
789Yellow 127
123Green 654
456Green 354
789Green 324

---

it is in a format that can be saved as tab separated and pulled into excell without any other work except the save of course!

here to help

thanks for ur code.

I have attached the file, am i missing something in this??

coz its not working for me.

many thanks and apologies for the delay in replying, since wasn't working over the weekend.

your tester has macros off...

the code i send does that task of collecting the data from the structure you showed on your post..

you need to alter the output of the routine so that it puts the stuff where you want it.

you will not see and output because it is sent to the immediate window for test purposes only.

here to help (if you need more to put stuff in appropriate place)

The macros are not off... its jst a window's security thing...
it asks for enabling it as a security measure.

I am still new to vba, so i don't know, how to alter the output or save it from what u have given me.

Please let me know... thanks.

ok - can you goto the macro editing bit in your excel?

if so this is where you will need to be to understand the code i sent and then we can look at what you need to make the appropriate output.

yes ofc,

the file i sent u had the macro in it... i put in your code in that file.

that is why i sent you.

I am able to see the output in the immediate window, by pressing ctrl+g,

but its not correct.
I will try and send another file, that will make it more clear.

please find attached a new file, which makes things mroe clear.

Please let me know, how to go forward from here.

Regards,
Andy

Hi Bill,

As discussed, please find the 3 files attached, which are the input and output files.

Many thanks for your help.