How to wisely copy input from one table to another.

[Rafa100]

How to wisely copy input from one table to another. I have attached excel file, could you please assist with it. I showed in the file the result I want to achieve.

[popipipo]

Try this formula:

Please Login or Register  to view this content.

[Rafa100]

This only helps to identify values in column E. Can we make it that all columns from B to E were inputted by the help of formula

[Rafa100]

Try this formula:

Please Login or Register  to view this content.

This only helps to identify values in column E. Can we make it that all columns from B to E were inputted by the help of formula

[Pete_UK]

You can use a helper column to identify which column from D to F contains a value, and give each record a unique sequential count. For example, put this formula in cell H6:

=IF(D6="",IF(E6="",IF(F6="","",300+COUNTIF(F$6:F6,"<>")),200+COUNTIF(E$6:E6,"<>")),100+COUNTIF(D$6:D6,"<>"))

and copy down to H20. Then you can use the following formulae in the cells stated:

B25: =IFERROR(INT(SMALL($H$6:$H$20,ROWS($1:1))/100),"")

C25: =IF(B25="","",IF(E25>=0,"Dr","Cr"))

D25: =IFERROR(INDEX(C$6:C$20,MATCH(100+ROWS($1:1),$H$6:$H$20,0)),IFERROR(INDEX(C$6:C$20,MATCH(200+ROWS($1:1)-COUNTIF($H$6:$H$20,"<200"),$H$6:$H$20,0)),IFERROR(INDEX(C$6:C$20,MATCH(300+ROWS($1:1)-COUNTIF($H$6:$H$20,"<300"),$H$6:$H$20,0)),"")))

E25: =IF(B25="","",IF(D25="","",VLOOKUP(D25,$C$6:$F$20,1+B25,0)))

Copy these 4 formulae down as far as you need to, as can be seen in the attached file - you might find that the commas ( , ) have been converted into semicolons ( ; ), depending on your regional settings.

Hope this helps.

Pete

[popipipo]

It works to get the values from columns D:G

[Rafa100]

It works to get the values from columns D:G

It is great. But if we have too many columns, inputting every column name would be very hard. impractible

[Rafa100]

Pete_UK,

It is impracticable, if we have too many columns, but it works with if we few columns. Do you know how can we configure the formula, so that it could take columns without limitiation.

[Pete_UK]

How many columns do you expect to have?

Pete

[Rafa100]

Around 140

[Rafa100]

Could you pls help with my request

[AliGW]

Rafa - please be patient! Your last post was yesterday evening (after working hours) and you are now pressing for an answer a few hours later before working hours.

Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

From the forum rules (link in the main menu above):

• If your question has not been answered within a day, consider adding another post with any additional information you believe is relevant. If you think your post is good as is, just reply to your own thread with the words "Bump no response", which will bring it to the top of the forum.

[Rafa100]

ALIGW, I did not intend to make pressure of time on anyone here. Just wrote again, to say that problem is not solved. I will be patient I promise. Have a nice day

[AliGW]

Thank you. However, we know the problem is not yet solved - you don't need to remind us. I am sure that Pete will respond once he is here. I doubt he's had his breakfast yet.

[Pete_UK]

Too right, Ali - at the time you posted (7:07am here), I was still in the Land of Nod.

I'll take another look at this, but with 140 columns it's probably not very suitable for a formula-based solution.

Pete

[Pete_UK]

@Rafa,

in your attachment (with only 3 adjustment columns) there is never more than one adjustment value for each of the descriptions in column C. Is this also the case for your real file of 140 adjustment columns, or might you have more than one adjustment for each record?

Do you also have more than the 15 descriptions which you show in column C ?

If your real file differs from the sample that you provided, it would be helpful if you could attach another sample which is more representative of what you really have.

Pete

[Rafa100]

Each Column represent one unique adjustment. Description in column C is more or less in the same quantity.

[Rafa100]

Pete

attached file is real life version

[JeteMc]

Perhaps this will be of help.
A helper row (39) is populated using:

Please Login or Register  to view this content.

The Adjustment column is populated by typing the number 1 into cell B45, then using the following in B46 and down:

Please Login or Register  to view this content.

Note: gaps are produced using conditional formatting: =D45="" (font set to white if TRUE).
The Amount column is populated using:

Please Login or Register  to view this content.

The Balance Sheet Item column is populated using:

Please Login or Register  to view this content.

The Description column is populated using: =IF(D45="","",INDEX(D$4:FH$4,B45))
The Dr/Cr column is populated as before.
Let us know if you have any questions.