CUT specific Row from a table and paste in another table in the same sheet based on value

**vikrampnz** · 09-27-2024, 11:46 AM

Hello

I have a spreadsheet with TWO tables with identical format and 600 odd rows showing "Unit Details" in each row, the second one is placed right below the first one in the same worksheet.
The last column of my table shows the status of each unit i.e. SOLD or UNSOLD.

The Top table shows Units with "SOLD" in Status column and the Bottom Table Only Shows units with "UNSOLD" in the status column.

Currently, I am doing this manually i.e. Once the unit is SOLD , I manually CUT it from the bottom table and paste in the Top table in Alphabetical sequence. Each unit should appear only Once i.e. either in the bottom table or in the top table.

Is there a way to automate this process i.e. If I Type "SOLD" in the Status column the entire row is shifted (Cut-Pasted, NOT Copy-pasted) from the bottom table to the Top Table and vice versa, if I type "UNSOLD" in status column that row should be in the bottom table already or shifted to Bottom table from Top table. The total number of units should always match the exact number i.e. can not be duplicated in both the tables.

I have attached a sample sheet showing two tables for your reference.

Thanks in Advance

V

**JohnTopley** · 09-27-2024, 11:59 AM

Please post a file which represents your actual layout: having tables one beneath another is not sensible (if this is the actual format).!

**vikrampnz** · 09-27-2024, 12:04 PM

Hello John, the file has actual format. The tables are below one another. Unfortunately Its the Government format that we have to follow. As its printed on the same page lateron while submitting the report.

**JohnTopley** · 09-27-2024, 12:11 PM

So if 3 UNSOLD are SOLD (in your example) what happens? So the starting rows of the tables dynmic with 2 rows between bottom Table 1 and top of Table 2 bottom?

**vikrampnz** · 09-27-2024, 02:07 PM

Any rows in bottom table that become "Sold" is shifted to the top table , in its ascending alphabetical position....

As the rows in top table increase, the bottom table is pushed further down always maintaining a gap of 2 or 3 rows between the tables...i.e. between last row of top table and header row of bottom table.

**Sintek** · 09-27-2024, 02:12 PM

This is quite a tricky one...Start with this...
Based on you making use of "SOLD" & "UNSOLD"

Please Login or Register  to view this content.

**JohnTopley** · 09-27-2024, 03:31 PM

A couple of "tweaks" (

....

Please Login or Register  to view this content.

**Sintek** · 09-27-2024, 03:38 PM

Am certain it will need a couple more...
Need to still identify how entries will be added...
What happens if all UNSOLD become SOLD and no row below headers etc etc...
Am certain there will be other scenarios to still take into account...

**vikrampnz** · 09-28-2024, 01:56 AM

Thanks a ton John... That Works Brilliantly. Exactly what I was trying to achieve.

Want to Request one more help if possible..... It shifts rows smoothly between SOLD & UNSOLD tables. My table has UNIT Nos ranging from A-101 to A-1306. (Approx. 78 Units). When I convert unit No. A-1001, A-1002,......A-1006 to SOLD, they are placed above A-101 instead of being place below the A-9 series i.e. A-901, 902, 903... etc

Could you please suggest how I can fix this? Thanks again for your prompt help.

**JohnTopley** · 09-28-2024, 03:09 AM

First and foremost, the code was from Sintek [Post #6 to which I added very minor "tweaks"] so ALL credit is due to him: and please read post #8 where Sintek poses questions that need to be answered.

The sort sequence is the common problem with sorting alpha-numeric data: I will leave any solution to Sintek to avoid any more answers being wrongly attributed to me.

**vikrampnz** · 09-28-2024, 03:25 AM

My sincere apologies Sintek for my ignorance and thanks John for pointing it out to me.

Could you please guide me Sintek?

Thanks

**Sintek** · 09-28-2024, 03:58 AM

And the plot thickens...A custom list sort would be ideal here...sadly it has it's limits...
Try this manipulation...must admit...i have never seen a sheet event this complex...
Would have beenn so much easier if the tables were on different tabs or next to each other...

Please Login or Register  to view this content.

**vikrampnz** · 09-28-2024, 12:21 PM

Hello Sintek, your latest code is working vwery well in sample sheet. But its causing a problem in my actual sheet as I have multiple prefixes i.e. A-101, B-101, C-101 and so on. When I added your new code to the sheet, its clubbing all the prefixes for a number in one cell ....i.e. A-B-C-101

Could you please help !

Thanks

**Sintek** · 09-28-2024, 12:37 PM

Oh my gosh...Really????

My table has UNIT Nos ranging from A-101 to A-1306. (Approx. 78 Units). When I convert unit No. A-1001, A-1002,......A-1006 to SOLD, they are placed above A-101 instead of being place below the A-9 series i.e. A-901, 902, 903... etc

So why not mention this in the first place...Obviously above will not work...

Hello Sintek, your latest code is working vwery well in sample sheet.

The joys of not being transparent from the getgo...

No time now...Will look at it after the weekend...I suggest uploading a sample file with actual data setup so no time is wasted on supplying incorrect code...Aswell as showing a before and after with this supposed sorting the way you want...
Perhaps someone else will hop on board and offer a solution...i'm guessing as a custom list is not possible and above cannot work...Either a helper column or a loop...

Either way...Just don't have the energy right now...

**vikrampnz** · 10-01-2024, 09:43 AM

My Sincere apologies Sintek for the trouble. Didnt realise the impact not mentioning various suffixes would have on the code.

As suggsted by you, I have attached a sample file with all the necessary details.

Thanks

**Sintek** · 10-01-2024, 09:51 AM

Hello Sintek, your latest code is working vwery well in sample sheet. But its causing a problem in my actual sheet as I have multiple prefixes i.e. A-101, B-101, C-101 and so on

No ... I'm sorry...This is even worse than initially posted...
I mean look at this...Your initial sample was entries consisting of A-101, A-102 etc...
You then mention different suffixes...I was taliking about prefixes...Now you come with this...

A-101
OFFICE 04
OFFICE 01-F
UL1
UL12
L12
L1201
B-1202

Now you even have pre & suf fixes, dashes|no dashes, spaces|no spaces????
And to add to that...now even a total row???
And also no before|after expected result shown suggesting that you yourself have no idea what the accurate sorting result should be...
This is a nightmare...I suggest you review your design and layout of this...

As per John...

The sort sequence is the common problem with sorting alpha-numeric data:

I agree especially with such vast different Alpha-Numeric sequences

**JohnTopley** · 10-01-2024, 11:25 AM

.... Even the table headings are not consistent!

**vikrampnz** · 10-02-2024, 02:16 AM

Since this format was used/updated manually all this time, it didnt matter if a few unit numbers missed a dash or headings were different. But now since I have taken over the sheet updating part, I will ensure that the unit number formats are consistent i.e. All have a dash, headings of the two tables are consistent etc.

If you see any other part of sheet that may potentially cause problems with the code, I can fix/modify that too, provided I know. I would appreciate you help !

**Sintek** · 10-02-2024, 02:39 AM

Look...As is, I can offer a solution...Am actually working on one now...

The issue was your lack of transparency and continued criteria changes...
What does it help asking for a solution if you don't give all the information...This is just a waste of time...

Yes, I suggest first fixing your file to have an accurate representation and then I will gladly continue...

**JohnTopley** · 10-02-2024, 06:06 AM

My attempt ..based on Sintek's code ...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**Sintek** · 10-02-2024, 06:43 AM

Let's hope the next sample file which depicts "accurate reflection of data" is similar to previous sample files...

**vikrampnz** · 10-03-2024, 11:34 AM

Thanks John its working perfectly and yes Sintek actual file is exactly same as this one ( except that now its cleaner and with no mistakes

)

Just one last thing John, the Dynamic total at the bottom of each table isnt updating automatically. I tried adding the sum function , but the sum range is not getting updated when the units are shifted from bottom table to top table.

**JohnTopley** · 10-03-2024, 01:20 PM

On checking the total (logic for) , I do not believe it is working correctly. I need to take another look at the logic for getting the start row/ end row for each table.

**Sintek** · 10-03-2024, 01:47 PM

Hey guys...No access to pc for the weekend...
@vikrampnz...
Pls upload a sample file representing actual Alphanumeric numbering in the meantime and file representing actual setup...Cannot imagine that you have all those empty rows above and empty columns to left...

**JohnTopley** · 10-03-2024, 02:30 PM

Try the attached

**JohnTopley** · 10-03-2024, 02:46 PM

UPDATED file ("SR No" corrected)

**Sintek** · 10-03-2024, 02:56 PM

@ John...
As you supplied I cannot imagine it to be the accurate representation as per OP comment...

except that now its cleaner and with no mistakes

Will await updated as per above...

**JohnTopley** · 10-03-2024, 03:06 PM

@Sintek: well... I gave it go!

**Sintek** · 10-03-2024, 03:26 PM

@ John...

I gave it go!

Yes...All worked as it should with supplied inaccurate representation of file...

Was merely addressing OP comments...

Since this format was used/updated manually all this time, it didnt matter if a few unit numbers missed a dash or headings were different. But now since I have taken over the sheet updating part, I will ensure that the unit number formats are consistent i.e. All have a dash, headings of the two tables are consistent etc.

Which you too eluded to...

.... Even the table headings are not consistent!

No new file since then...No use wasting time unless OP supplies his accurate file...

**vikrampnz** · 10-04-2024, 05:20 AM

Hello, sorry for late response....was travelling.

I have attached a clean file with exact consistent data. I have removed all the previous codes from it to keep it simple.

I have made following changes to it, which were not there before:

1) Some unit numbers have an alphabet suffix "F" e.g. "OFF-1F". Prefixes remain to be A,B,C,L,U,OFF.

2) Status Has two more categories - "Consideration" & "GOVT." ....... these units will always remain in the bottom table as they are not for Sale.

I am assuming that anything other than "SOLD" will remain in the bottom table and hopefully having more
status categories wont affect the code. "UNSOLD", "CONSIDERATION" & "GOVT." will always remain in bottom table.

3) I have added a "TOTAL UNITS" field (which is a total of units in top table & bottom table) in the left top corner of the sheet to tally that the total number of units hasn't changed.

The columns that are left blank to the left of the tables and blank rows above top table and bottom table are required like that as my table headings go there.

Hope this is in line with your requirement Sintek & John. If you need me clarify anything else, please let me know.

Thanks a lot

**Sintek** · 10-04-2024, 05:53 AM

The columns that are left blank to the left of the tables and blank rows above top table and bottom table are required like that as my table headings go there.

So why not add them as per original file setup...All this plays a role in correct provided code...
Please...Supply a sample file that represents your actual file setup...
As per above one can see that this has changed so so so much since first post...HELP US HELP YOU...

**JohnTopley** · 10-04-2024, 08:16 AM

See attached but I echo Sintek's request that you post a sample file that represents your actual file setup.

**vikrampnz** · 10-04-2024, 09:09 AM

Dear Sintek & John, the file attached today is exactly what the original file looks like....nothing less, nothing more !

**JohnTopley** · 10-04-2024, 09:28 AM

No file attached to last post.

**vikrampnz** · 10-04-2024, 11:54 AM

There is a file called "Table Sample V.02.xlsm" which was attached to my post with a long explanation.

**vikrampnz** · 10-04-2024, 11:57 AM

Thanks John & Sintek for your patience and help ! John, your last file works like a dream.

Thank you both for patiently putting up with the shortcomings in the data supplied by me.

**JohnTopley** · 10-04-2024, 12:14 PM

Good news!

For future reference: post files which reflect your actual file to avoid any respondents possibly wasting time and effort constantly revising solutions.

**Sintek** · 10-06-2024, 07:05 AM

I see John has solved...Glad you got it sorted...
Just for closure...From my side...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

CUT specific Row from a table and paste in another table in the same sheet based on value

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Re: CUT specific Row from a table and paste in another table in the same sheet based on va

Thread Information

Users Browsing this Thread

Similar Threads

Bookmarks

Bookmarks

Posting Permissions