We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

VBA Excel 2010 - "Crosstab" in Excel

csehz
csehz asked
on
Medium Priority
352 Views
Last Modified: 2012-05-11
Dear Experts,

Can you please have a short look on the attached file, basically if somebody is familiar its a download from SAP, where items have stock on different storage locations, but being in a single field.

The pattern of it is always

1) text "SLOC"
2) number of the SLOC and ":"
3) stock
4) ","

So for example the cell "SLOC4:-2.947, SLOC3:201.121, SLOC1:3.414" means that item has minus 2.947 stock on SLOC4, stock 201.121 but on SLOC3, and 3.414 on SLOC1.

Could you advise whether with VBA or formula, is it possible to write some logic which would result as I did manually the C-D-E-F columns? So cutting the stock numbers out and putting under the certain SLOC column.

I assume rather it is complex but maybe someone has idea

thanks,
ExcelCrosstab.xls
Comment
Watch Question

Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Saqib HusainEngineer
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
csehzIT consultant

Author

Commented:
This forum is amazing :-) From where I should have 1500 points? :-)))

I even almost did not have dare to put this question, and got three working solutions in so short and almost the same time. I think the fair to split the points as equal, thanks very much.

And Happy Easter too :-)
Both the formulas with fail for values like

SLOC3:-9.32615623, SLOC2:93

Sid
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
How come Siddharth? it works fine with me.
My Mistake. Yeah you are right. I didn't autofit the columns. It was showing truncated values :)

Sid
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.