Copy and arrange cells in unique way

[Geoffrey Baker]

I am starting an excel file that extracts file from another worksheet, then after it is extracted, A2 cell has a value in cell B2, I wanted to copy all the cells in column A&B to column C&D in such a way that when the word "check is encountered in column A, it will arrange itself in the last with the other cells that contain same words before the word "check". I wanted to do this in macro or formula if possible. I have tried to look in the forums but I can't find a closer solution to this. Please help..Can anyone please help me on

[DonkeyOte]

Not entirely sure I follow... you didn't list TO1/TO2 so unsure as to significance of the ommission...

Based entirely upon your sample file one possible approach might be to use a quick helper calc so as to ease the data retrieval process (avoiding complex arrays etc)

Please Login or Register  to view this content.

Then for your table

Please Login or Register  to view this content.

[Geoffrey Baker]

Thanks very much for that. That seems to do the trick.
I've got another question, What is the best formula to use if I want to get the value of a cell from another open workbook?that other workbook is in the same directory or folder as the main file? I've tried to use this but it does'nt work:
=INDIRECT("'...\[TS.xls]TS'!A2")

[DonkeyOte]

It would seem that a simple link should suffice (ie no need for Volatile INDIRECT function), eg type = and subsequently navigate to cell of interest in the other file and click on it.

Going forward please keep threads dedicated to the original question - if you have other questions to ask please create new threads accordingly.

[Geoffrey Baker]

what if the cells are links to other workbook?The value of the cells are from other workbook?Will this work?what can I include for this to work?Thanks man.

[Geoffrey Baker]

Sorry for that...Referring to the trick you've done in arranging the cells,what if the cells in column A & B are links to other workbook?The value of the cells are from other workbook?Will this work?what can I include for this to work?Thanks man.

[Geoffrey Baker]

Hi Donkey Ote, I was trying the formula you've posted but when I've entered 12837 GO1 instead of just GO1 etc., it reflects a N/A error? is it not applicable when I've got numbers in front of the string?I hope you could help me on this,I've tried already changing the lookup value but it does'nt help...I hope you could enlighten me on this. Thanks...

[DonkeyOte]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

[Geoffrey Baker]

Here you go...I'm still new to this forum,sorry for the mistakes..Thanks man...

[DonkeyOte]

Can you confirm then that cells A8 and A9 contain typo's ?
ie should commence with 1283709_NP where _ equates to a space (per the CHECK line)

[Geoffrey Baker]

Yup it should be 1283709 NP COKE 1800-600 17/9,just typo error..Thanks..

[DonkeyOte]

In which case if you're looking to adapt the "index" formula from before then:

Please Login or Register  to view this content.

If you're looking to insert rows etc between groups then in all honesty I would probably suggest looking at VBA.

[Geoffrey Baker]

That's really great man. Thanks. I really appreciate it.