• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Working with strings, can not define criteria for search

ThrottleWorks

ThrottleWorks

Excel Ninja
Hi,

I have two columns. Column 1 represents currency and 2nd column represent amount for that currency.

No of columns in the worksheet are not fixed. They can be between 10 to 60.
Serial of column is also not fixed.

I have currency header as ‘ABC CASH USD (CASH - USD)/XYZ1’ at column B
I have amount header as ‘ABC CASH USD (CASH - USD)/PPP1/OOO’ at column D

Amount is always 2 columns next to the currency it represents. Format of both the string remains same. Apart from currency, rest of the words are static in both the combinations.

The first value represents currency and second value represents amount.

In the input file, there can be multiple pairs for currencies and their amounts such as USD, SEK, PLN, AUD ETC.

It is quite possible, file will not have any pair for currency, or only one pair or multiple pairs.

I am required to populate a grid based on the pairs present in input file.

For example, if I have 5 pairs in input file, my grid will show 5 pairs.

My doubt is how will I identify number of pairs in the file and once I know number.
How do I pick all the pairs correctly for populating in grid.

I guess, partial countif will not work in this. Can anyone please help me this.
 
Hi @Hui sir, sorry for late reply. I am sorry I got confused here.
In Input sheet, there will be more rows in real scenario. But as of now I need to find out number of pairs.

Once I get possible combinations I will pick the values in the row accordingly. As of now, I am not able understand, how do I prepare grids for n number of pairs. As seen in Output worksheet.

In output sheet, row 2 and 3 are correct. These will be populated like this only. From row 4 onwards, numbers will get changed, but I guess, I will manage that.

Good night. :)
 
Hi @Hui sir, I guess, I will be able to do it. It is not efficient way but at present will work for me.

Transpose all the headers in new sheet. Apply contain filter on ABC CASH.
Take these headers on new sheet, apply does not contain )/PPP1/OOO’

This way I will get number of pairs present in a worksheet.

Thanks for the help. Have a nice da ahead. :)

PS - It clicked to me while riding back to home yesterday night. :p
 
You must log in or register to reply here.
Back
Top