Transpose Cell's Value's Last 2 digit Transpose to columns -Till Next Main Value Change

[Chirag R Raval]

Dear Sir,

I have a file (Attached) , try to transpose column B's data but require transpose last 2 digits in columns till Change Next Main Unique value in Columns "B" , Means Transpose last 2 digit in columns for till series end of main value ...

like below
Sr.No. Material REQUIREMENT MATERAL NO AS BELOW

1 0077493-01 077493 01 02 03 04 05

1 0077493-02 077535 01 02 03

1 0077493-03 077535 01 02 03 04

1 0077493-04

1 0077493-05

2 0077535-01

2 0077535-02

2 0077535-03

3 0077536-01

3 0077536-02

3 0077536-03

3 0077536-04
Another approach....
if suppose last 2 digits as in next columns how to transpose that column based on every changes in previous column?

so there are need 2 solutions
(1) if data combine in 1 column how to transpose last 2 digits in columns?
(2) if data in 2 columns , how to transpose 2nd column based on 1st column's value change?
can this done by Excel Macro?
help will be appreciated..

Regards,
Chirg Raval

 

[vletm]

Chirag R Raval
(1) Something like this?
(2) without sample data & result ... answers are carefully and yes!

 

[Chirag R Raval]

Dear Sir,

Perfect.& Awesome... how to present thanks ?
really you help me ...great...you not know that your little this help how tremendous helpful to me..

Actually this is process of transformation Price List to Order Form. Many times I get Material No (first 7digits) And "-" and Shade Nos (last 2 digits-after "-") in combined form so if I want to convert at tabular for that make order form so buyers can put their ordered units in each shade No Block ..

sorry for forgot..to mentioned in my question but if Sr. No (Serial Nos) also
require to put before this. Please beware that sr nos not always in sequence till last so it .must be catch from sr no columns ...

many time I received same thing (result of transformation)..then there are need to convert it from order form to price list...
if possible can this process can be reverse? if I receive same shade Nos in columns can I convert (Transform) it in 1 column? as combination of Material no & "-" & shade no ? with sr no?

help will be appreciated...

Regards,
Chirag Raval

 

[Chirag R Raval]

Dear Sir,

Above macro (This Thread's your post no 2) work perfectly but ...but result not as per expected ...whole thing is that I get shade no's (last 2 digits) not always in sequence..& this macro code make blank blocks ..between 2 shads ..there are should not blank block between 2 shades , all shade no's block need to in sequence & must take from column's Shade No ..as in material columns

hope you understand. What I mean..

Regards,

Chirag Raval

 

[vletm]

You would try to make correct sample outputs!

 

[Chirag R Raval]

Dear Sir Vletm,

Thanks for encourage me to struggle & cop with this matter..
your post no 5 of this thread says "seems that solution already is in code.
.if little modification if I can do..."

I will try my best sir, & if stop somewhere..i will mentioned in this thread..

Thanks again for you effort & help..

Regards,
Chirag Raval

 

[vletm]

Okay - no sample ...
There are reasons why those blanks ...
and
I also understand, why no need blanks ...
but
if no need banks then there have to do more... before test my solution.
Or just take 'a risk' and do like this sample...

 

[Chirag R Raval]

Dear Sir,

Great...amazing...miracles happen. Same as desired.
(& also logically & technically requirement).

Actually ..firstly I can not understand about your wishing about
"Sample Required" because I already attached my sample file with requirement in it...& sorry for that "why I not reply that I already attached sample?"

serial no can be add? [as per files column's -(Which not always in Sequence)
NOT Auto-Generate]
(please refer my Post No 3...)

hope little more for serial nos ...

Regards,
Chirag Raval

 

[vletm]

Sample Required:
Actually ..firstly I can not understand about your wishing about
"Sample Required" because I already attached my sample file with requirement in it...& sorry for that "why I not reply that I already attached sample?"
... which has mistake!
>> This is a sample of sample which MAYBE I have waited ... waited ... <<

Do this look again ... something ... that You're looking for?
Or
Could You finally give someway 'nonverbal' sample?

 

[Chirag R Raval]

Dear Sir,

absolutely. You are right..(as per blue) ..which I desired....

oh ...how precise you want.... another time I caught in mistake. for not give sample..(with sr nos) ..that what you want..& really deeply sorry that cannot understand this time also...(Sample of Sr Nos) & thanks for your effort for even sample you prepare for me ...

your code runs marvellously .. but

i check this macro after copy paste in my general module (personnel.xlsb) ..
VBA always want Variable declarations so ...i declare as per below.

Dim a_tab As String
Dim y_max As Long
Dim x_max As Long
Dim k As range
Dim y As Integer
Dim o_max As Integer
Dim chk_b As range
Dim chk_k As String
Dim chk_o As String
Dim o_chk As Integer
Dim y1 As Integer
Dim x1 As Integer
Dim y2 As Integer
Dim x2 As Integer

but result can not generate as marvellously yours (your provided xl file)

Regards,

Chirag Raval

 

[vletm]

> without clear sample will get only guesses <
compare to my code ...
why k as range?
why chk_b as range?
chk_k & chk_o NOT strings!

 

[Chirag R Raval]

Dear Sir,

Marvellous ....Perfect.... Great....as desired ....with Sr Nos...Final....

if copy paste your code in personnel.xlsb...

k = WorksheetFunction.Match(chk_k, Sheets(a_tab).Range("L:L"), 0)

...so i firstly declare as WorksheetFunction...but....
then it get (redim) as k = .Cells(.Rows.Count, 12).End(xlUp).Row + 1
so re-guess it will be range....

chk_b = .Cells(y, 2) .... so i guess its Range ....
chk_k = Val(Mid(chk_b, 2, 6)).....first i already as string but....
chk_o = Val(Mid(chk_b, 9, 2))...)).....first i already as string but....

i already try above As...this....As this... but all time scared result generate
in file....

can i run this code in personnel.xlsb??

hope your co-operation...

Regards,
Chirag Raval

 

[vletm]

... You didn't read my previous Reply or how?
...
and I cannot know about Your 'personnel.xlsb'-file

 

[vletm]

...
What is k in 'my' and 'Your' code? Have You thought a second or two?
'Your way' ... how many rows will come? ... as many as source!
chk_k & chk_o ... would You check what will val-function do?

 

[Chirag R Raval]

Dear sir vletm,

Thanks.....
K can be declare as long or may be integer?
Due to its return count's result in form of
Nos (Digit)? ..it's surly says "next" by adding
1 in loop (as your way "current position no + 1"= next cell "

Chk_k & chk_o just put an extracted value to cell or in other
Word both assigns value to the cell

Regards,
Chirag Raval

 

[vletm]

> Your k counts ... and You'll get always new row!
If You like many many rows then okay?
> You didn't check that function ... okay!

 

[Chirag R Raval]

Dear Sir,

I Declare as below & its run

Dim a_tab As String
Dim y_max As Long
Dim x_max As Long
Dim k As Double
Dim y As Integer
Dim o_max As Integer
Dim chk_b As String
Dim chk_k As Double
Dim chk_o As String
Dim o_chk As Integer
Dim y1 As Integer
Dim x1 As Integer
Dim y2 As Integer
Dim x2 As Integer

but..."would You check what will val-function do?"
I will check & revert what it is do?

Thanks again ..for your tremendous help ..

Regards,
Chirag Raval

 

[Chirag R Raval]

Dear Sir,


found on web for what val function do in VBA...


The Microsoft Excel VAL function accepts a string as input and returns the numbers found in that string.

The VAL function is a built-in function in Excel that is categorized as a String/Text Function. It can be used as a VBA function (VBA) in Excel. As a VBA function, you can use this function in macro code

The syntax for the VAL function
Val( string )
Parameters or Arguments
string :- A string expression that you wish to return the numbers found within.
REMEMBER

• The VAL function will stop reading the string once it encounters the first non-numeric character ..This does not include spaces.
• Type of Function
  • VBA function (VBA)
  Example (as VBA Function)
  The VAL function can only be used in VBA code in Microsoft Excel.
• Examples:-

Val("10 Terminator")
Result: 10

Val("34 10 Terminator")
Result: 3410

Val(" 34 10 Terminator")
Result: 3410

Val(" 34 - 10 Terminator")
Result: 34

Val("075")
Result: 75


Regards,
Chirag Raval